473,699 Members | 3,282 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

bulk copy from flat file to postgres dbserver

37 New Member
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
7 5688
twinklyblue
37 New Member
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
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.
Jul 14 '07 #3
twinklyblue
37 New Member
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
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:
Expand|Select|Wrap|Line Numbers
  1. select version();
  2.  
Jul 19 '07 #5
twinklyblue
37 New Member
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:
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
michaelb
534 Recognized Expert Contributor
twinklyblue, what platform are you running on?
Jul 20 '07 #7
twinklyblue
37 New Member
I'm running on Linux 8.0
Jul 23 '07 #8

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

Similar topics

16
17011
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...
2
9733
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 --------------------------------------------------------------------------------------------------------
1
5617
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?
2
2916
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.
2
3038
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
9
10026
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...
11
28335
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');
3
7674
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:
0
1715
DTV12345
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
0
9053
jinu1996
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...
0
8894
tracyyun
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...
0
7776
agi2029
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...
1
6540
isladogs
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...
0
5879
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();...
0
4636
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3071
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
2
2360
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2015
bsmnconsultancy
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...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.