pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
populate table with large csv file
6+ messages / 4 participants
[nested] [flat]

* populate table with large csv file
@ 2003-09-25 16:38  Dave [Hawk-Systems] <dave@hawk-systems.com>
  0 siblings, 3 replies; 6+ messages in thread

From: Dave [Hawk-Systems] @ 2003-09-25 16:38 UTC (permalink / raw)
  To: pgsql-general@postgresql.org

have the table "numbercheck"
	 Attribute |    Type    | Modifier
	-----------+------------+----------
	 svcnumber | integer    | not null
	 svcqual   | varchar(9) |
	 svcequip  | char(1)    |
	 svctroub  | varchar(6) |
	 svcrate   | varchar(4) |
	 svcclass  | char(1)    |
	 trailer   | varchar(3) |
	Index: numbercheck_pkey

also have a csv file
	7057211380,Y,,,3,B
	7057216800,Y,,,3,B
	7057265038,Y,,,3,B
	7057370261,Y,,,3,B
	7057374613,Y,,,3,B
	7057371832,Y,,,3,B
	4166336554,Y,,,3,B
	4166336863,Y,,,3,B
	7057201148,Y,,,3,B

aside from parsing the csv file through a PHP interface, what isthe easiest way
to get that csv data importted into the postgres database. thoughts?

thanks

Dave





^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: populate table with large csv file
@ 2003-09-25 16:43  Joe Conway <mail@joeconway.com>
  parent: Dave [Hawk-Systems] <dave@hawk-systems.com>
  2 siblings, 0 replies; 6+ messages in thread

From: Joe Conway @ 2003-09-25 16:43 UTC (permalink / raw)
  To: Dave [Hawk-Systems] <dave@hawk-systems.com>; +Cc: pgsql-general@postgresql.org

Dave [Hawk-Systems] wrote:
> aside from parsing the csv file through a PHP interface, what isthe easiest way
> to get that csv data importted into the postgres database. thoughts?
> 

see COPY:
http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=sql-copy.html

Joe




^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: populate table with large csv file
@ 2003-09-25 16:50  P.J. "Josh" Rovero <rovero@sonalysts.com>
  parent: Dave [Hawk-Systems] <dave@hawk-systems.com>
  2 siblings, 1 reply; 6+ messages in thread

From: P.J. \"Josh\" Rovero @ 2003-09-25 16:50 UTC (permalink / raw)
  To: Dave [Hawk-Systems] <dave@hawk-systems.com>; +Cc: pgsql-general@postgresql.org

Dave [Hawk-Systems] wrote:

> aside from parsing the csv file through a PHP interface, what isthe easiest way
> to get that csv data importted into the postgres database. thoughts?

Assuming the CSV file data is well formed, use psql and
the COPY command.

In psql, create the table.  Then issue command:

copy <tablename> from 'filename' using delimiters ',';
-- 
P. J. "Josh" Rovero                                 Sonalysts, Inc.
Email: rovero@sonalysts.com    www.sonalysts.com    215 Parkway North
Work: (860)326-3671 or 442-4355                     Waterford CT 06385
***********************************************************************




^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: populate table with large csv file
@ 2003-09-25 16:57  Ron Johnson <ron.l.johnson@cox.net>
  parent: Dave [Hawk-Systems] <dave@hawk-systems.com>
  2 siblings, 0 replies; 6+ messages in thread

From: Ron Johnson @ 2003-09-25 16:57 UTC (permalink / raw)
  To: PgSQL General ML <pgsql-general@postgresql.org>

On Thu, 2003-09-25 at 11:38, Dave [Hawk-Systems] wrote:
> have the table "numbercheck"
> 	 Attribute |    Type    | Modifier
> 	-----------+------------+----------
> 	 svcnumber | integer    | not null
> 	 svcqual   | varchar(9) |
> 	 svcequip  | char(1)    |
> 	 svctroub  | varchar(6) |
> 	 svcrate   | varchar(4) |
> 	 svcclass  | char(1)    |
> 	 trailer   | varchar(3) |
> 	Index: numbercheck_pkey
> 
> also have a csv file
> 	7057211380,Y,,,3,B
> 	7057216800,Y,,,3,B
> 	7057265038,Y,,,3,B
> 	7057370261,Y,,,3,B
> 	7057374613,Y,,,3,B
> 	7057371832,Y,,,3,B
> 	4166336554,Y,,,3,B
> 	4166336863,Y,,,3,B
> 	7057201148,Y,,,3,B
> 
> aside from parsing the csv file through a PHP interface, what isthe easiest way
> to get that csv data importted into the postgres database. thoughts?

No matter what you do, it's going to barf: svcnumber is a 32-bit
integer, and 7,057,211,380 is significantly out of range.

Once you change svcnumber to bigint, the COPY command will easily
suck in the csv file.

-- 
-----------------------------------------------------------------
Ron Johnson, Jr. ron.l.johnson@cox.net
Jefferson, LA USA

"Python is executable pseudocode; Perl is executable line noise"




^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: populate table with large csv file
@ 2003-09-26 11:58  Dave [Hawk-Systems] <dave@hawk-systems.com>
  parent: P.J. "Josh" Rovero <rovero@sonalysts.com>
  0 siblings, 1 reply; 6+ messages in thread

From: Dave [Hawk-Systems] @ 2003-09-26 11:58 UTC (permalink / raw)
  To: pgsql-general@postgresql.org

>> aside from parsing the csv file through a PHP interface, what isthe
>easiest way
>> to get that csv data importted into the postgres database. thoughts?
>
>Assuming the CSV file data is well formed, use psql and
>the COPY command.
>
>In psql, create the table.  Then issue command:
>
>copy <tablename> from 'filename' using delimiters ',';

perfect solution that was overlooked.

Unfortunately processing the 143mb file which would result in a database size of
approx 500mb takes an eternity.  As luck would have it we can get away with just
dropping to an exec and doing a cat/grep for any data we need...  takes 2-3
seconds.

the copy command is definately a keeper as I am not looking at replacing code
elsewhere with a simpler model using that.

Thanks

Dave





^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: populate table with large csv file
@ 2003-09-26 12:37  Ron Johnson <ron.l.johnson@cox.net>
  parent: Dave [Hawk-Systems] <dave@hawk-systems.com>
  0 siblings, 0 replies; 6+ messages in thread

From: Ron Johnson @ 2003-09-26 12:37 UTC (permalink / raw)
  To: PgSQL General ML <pgsql-general@postgresql.org>

On Fri, 2003-09-26 at 06:58, Dave [Hawk-Systems] wrote:
[snip]
> Unfortunately processing the 143mb file which would result in a database size of
> approx 500mb takes an eternity.  As luck would have it we can get away with just

Something's not right, then.  I loaded 30GB in about 8 hours, on
a slow system, with non-optimized IO.  Did you drop the indexes
first?

-- 
-----------------------------------------------------------------
Ron Johnson, Jr. ron.l.johnson@cox.net
Jefferson, LA USA

"As the night fall does not come at once, neither does 
oppression. It is in such twilight that we must all be aware of 
change in the air - however slight - lest we become unwitting 
victims of the darkness."
Justice William O. Douglas




^ permalink  raw  reply  [nested|flat] 6+ messages in thread


end of thread, other threads:[~2003-09-26 12:37 UTC | newest]

Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-09-25 16:38 populate table with large csv file Dave [Hawk-Systems] <dave@hawk-systems.com>
2003-09-25 16:43 ` Joe Conway <mail@joeconway.com>
2003-09-25 16:50 ` P.J. "Josh" Rovero <rovero@sonalysts.com>
2003-09-26 11:58   ` Dave [Hawk-Systems] <dave@hawk-systems.com>
2003-09-26 12:37     ` Ron Johnson <ron.l.johnson@cox.net>
2003-09-25 16:57 ` Ron Johnson <ron.l.johnson@cox.net>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox