By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,158 Members | 917 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,158 IT Pros & Developers. It's quick & easy.

bulk copy from flat file to postgres dbserver

P: 37
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.
Jul 10 '07 #1
Share this Question
Share on Google+
7 Replies


P: 37
Hi All,

After a few readings, I found out about the copy command. I tried executing this code but encountered an error.

Expand|Select|Wrap|Line Numbers
  1. 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?
Jul 10 '07 #2

Expert 100+
P: 534
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.
Jul 14 '07 #3

P: 37
Hi!

I tried the copy command for the uploading of the data with the following commands used:

Expand|Select|Wrap|Line Numbers
  1. copy area_new from '/var/lib/pgsql/KEN_ALLpk.txt' with delimiter ';';
then I encountered the following error following the execution of this command:

Expand|Select|Wrap|Line Numbers
  1. 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!
Jul 18 '07 #4

Expert 100+
P: 534
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 programmatically, 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:
Expand|Select|Wrap|Line Numbers
  1. select version();
  2.  
Jul 19 '07 #5

P: 37
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 programmatically, 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:
Expand|Select|Wrap|Line Numbers
  1. select version();
  2.  

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?
Jul 20 '07 #6

Expert 100+
P: 534
twinklyblue, what platform are you running on?
Jul 20 '07 #7

P: 37
I'm running on Linux 8.0
Jul 23 '07 #8

Post your reply

Sign in to post your reply or Sign up for a free account.