pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedpopulate 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