Hi The Scripts team,
I would like to know if there is a same function like bcp (for sybase and mssql 2k) for postgres? Is there any way that I can copy my csv datafile into the postgres database? For anyone who knows, thanks as always.
7 5688
Hi All,
After a few readings, I found out about the copy command. I tried executing this code but encountered an error. - copy ken_all from '/var/lib/pgsql/KEN_ALL.CSV';
error msg:
ERROR: invalid memory alloc request size 1073741824
is there something wrong with my script?
michaelb 534
Recognized Expert Contributor
It's hard to say, but I would start with looking at your data file.
Check on the separators, escape characters, etc.
Look at the COPY man page for reference.
Hi!
I tried the copy command for the uploading of the data with the following commands used: - copy area_new from '/var/lib/pgsql/KEN_ALLpk.txt' with delimiter ';';
then I encountered the following error following the execution of this command: - ERROR: invalid memory alloc request size 1073741824
I am not sure where the error is.. but this is how one of my rows looks like. There are about 200k+ of this same data.
0600000;北海道;札幌市 中央区;以下に掲載 がない場合
(this is in Japanese.. I am not sure if this will display properly)
Many thanks for your reply as always!
michaelb 534
Recognized Expert Contributor
Something rings the bell about this error -
"ERROR: invalid memory alloc request size some_number"
but I think this was in some 7.x version and most likely has been long fixed.
The very first thing I would try to do is to interrogate this file, either programmaticall y, or manually, depending on its size.
You want to make sure that fields are indeed separated with semicolon and you don't have a single field that runs for miles.
One common technique is to split file into fragments and load them individually into a test table. As dummy as it sounds this often helps to locate the problem when more sophisticated approach is unattainable.
It may help if you tell what version of Postgres you have and on which platform or OS you are running it. If you are not sure try this:
Something rings the bell about this error -
"ERROR: invalid memory alloc request size some_number"
but I think this was in some 7.x version and most likely has been long fixed.
The very first thing I would try to do is to interrogate this file, either programmaticall y, or manually, depending on its size.
You want to make sure that fields are indeed separated with semicolon and you don't have a single field that runs for miles.
One common technique is to split file into fragments and load them individually into a test table. As dummy as it sounds this often helps to locate the problem when more sophisticated approach is unattainable.
It may help if you tell what version of Postgres you have and on which platform or OS you are running it. If you are not sure try this:
hi michaelb,
thanks for your reply as always. My postgres version is 7.4.1. Do you think this is the version that has this problem?
I followed your suggestion and uploaded the file in batches. First, 100 lines, then I went on on until I got the error when I was uploading about 1000 lines. Then I found that the limit is 600 lines per batch. given this statistics, it is nearly impossible to upload 200k lines by batches of 600 lines.
Is there any other way to upload this huge file?
michaelb 534
Recognized Expert Contributor
twinklyblue, what platform are you running on?
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Philip Boonzaaier |
last post by:
I want to be able to generate SQL statements that will go through a list of
data, effectively row by row, enquire on the database if this exists in the
selected table- If it exists, then the colums must be UPDATED, if not, they
must be INSERTED.
Logically then, I would like to SELECT * FROM <TABLE>
WHERE ....<Values entered here>, and then IF FOUND
UPDATE <TABLE> SET .... <Values entered here> ELSE
INSERT INTO <TABLE> VALUES <Values...
|
by: Don Isgitt |
last post by:
Hi,
I am recreating a database and its applications onto a new server and
new software; old version of pg was 7.2.4 and new version is
gds2=# select version();
version
--------------------------------------------------------------------------------------------------------
|
by: Knepper, Michelle |
last post by:
Hi out there,
I'm a first-time user of the "Copy ... From..." command, and I'm trying
to load a table from a text flat file.
http://www.postgresql.org/docs/7.4/static/sql-copy.html
I don't know if I'm using the command correctly.
Question: I run this command as a superuser, and the "copy from"
command is run like a SQL command on the command line, correct?
|
by: Knepper, Michelle |
last post by:
Hi,
I'm a first-time user of the "Copy ... From..." command, and I'm trying
to load a table from a text flat file.
http://www.postgresql.org/docs/7.4/static/sql-copy.html
I'm getting the following error. Any help will be appreciated. Thank
you.
|
by: Kevin Bartz |
last post by:
Hi Postgressers! I really like Postgres. Thanks for all your work on it. I
just have a problem with the way it's handling my flat file's timestamp
columns.
I have a flat file with a column with dates formatted like this:
2004-04-15 18:04:26 PM
It's a bit strange, I know, but I didn't create the file. My idea of
Postgres's proper behavior would be to load this date as a military time
| |
by: David Rysdam |
last post by:
I have a large amount of data that I copy in and out of Sybase very
often. Now I also want to copy this data in and out of postgres. I
have an existing script that creates the entire database(s) from scratch
in Sybase and then uses the Sybase bulk copy tool "bcp" to copy the data
in.
I already did a brute force port of this script to postgres once, but
I'm trying to do it more elegantly now that I know what issues I'm going
to run...
|
by: Ted |
last post by:
OK, I tried this:
USE Alert_db;
BULK INSERT funds FROM 'C:\\data\\myData.dat'
WITH (FIELDTERMINATOR='\t',
KEEPNULLS,
ROWTERMINATOR='\r\n');
|
by: Davor |
last post by:
I'm trying to import data from flat file in table and have few
problems.
1. Field Delimiter is ',' (comma). If ',' occurs in quoted
string it is still treated as field delimiter. This is BUG or ?
2. In table I have datetime field that can be null, but bulk
insert reports error if in flat file is null or ''. It's OK only when
real date is specified.
Table:
|
by: DTV12345 |
last post by:
Hello!
I have been assigned to bulk-load data into several typed tables of our ODS. I am not clear as to whether it can be done.
Here's my homework ===>>>
The easy way is to perform INSERT statements manually, like this
insert into
callbacks
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
| |
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |