bulk copy from flat file to postgres dbserver

[twinklyblue]

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.

[twinklyblue]

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]

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.

[twinklyblue]

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]

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:
[CODE=sql]
select version();
[/CODE]

[twinklyblue]

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:
[CODE=sql]
select version();
[/CODE]

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]

twinklyblue, what platform are you running on?

[twinklyblue]

I'm running on Linux 8.0