pg.ddx.io pgsql-hackers@postgresql.org mailing list archive
help / color / mirror / Atom feedPostgresql bulk fast loader
5+ messages / 5 participants
[nested] [flat]
* Postgresql bulk fast loader
@ 2001-07-11 00:05 Naomi Walker <nwalker@eldocomp.com>
0 siblings, 2 replies; 5+ messages in thread
From: Naomi Walker @ 2001-07-11 00:05 UTC (permalink / raw)
To: pgsql-hackers
Does postgresql have any sort of fast bulk loader?
--
Naomi Walker
Chief Information Officer
Eldorado Computing, Inc.
602-604-3100 ext 242
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Postgresql bulk fast loader
@ 2001-07-11 00:41 mlw <markw@mohawksoft.com>
parent: Naomi Walker <nwalker@eldocomp.com>
1 sibling, 1 reply; 5+ messages in thread
From: mlw @ 2001-07-11 00:41 UTC (permalink / raw)
To: Naomi Walker <nwalker@eldocomp.com>; +Cc: pgsql-hackers
Naomi Walker wrote:
>
> Does postgresql have any sort of fast bulk loader?
It has a very cool SQL extension called COPY. Super fast.
Command: COPY
Description: Copies data between files and tables
Syntax:
COPY [ BINARY ] table [ WITH OIDS ]
FROM { 'filename' | stdin }
[ [USING] DELIMITERS 'delimiter' ]
[ WITH NULL AS 'null string' ]
COPY [ BINARY ] table [ WITH OIDS ]
TO { 'filename' | stdout }
[ [USING] DELIMITERS 'delimiter' ]
[ WITH NULL AS 'null string' ]
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Postgresql bulk fast loader
@ 2001-07-11 01:02 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Naomi Walker <nwalker@eldocomp.com>
1 sibling, 0 replies; 5+ messages in thread
From: Bruce Momjian @ 2001-07-11 01:02 UTC (permalink / raw)
To: Naomi Walker <nwalker@eldocomp.com>; +Cc: pgsql-hackers
> Does postgresql have any sort of fast bulk loader?
COPY command.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Postgresql bulk fast loader
@ 2001-07-11 13:38 Mark Volpe <volpe.mark@epa.gov>
parent: mlw <markw@mohawksoft.com>
0 siblings, 1 reply; 5+ messages in thread
From: Mark Volpe @ 2001-07-11 13:38 UTC (permalink / raw)
To: mlw <markw@mohawksoft.com>; +Cc: Naomi Walker <nwalker@eldocomp.com>; pgsql-hackers
Avoid doing this with indexes on the table, though. I learned the hard way!
Mark
mlw wrote:
>
> Naomi Walker wrote:
> >
> > Does postgresql have any sort of fast bulk loader?
>
> It has a very cool SQL extension called COPY. Super fast.
>
> Command: COPY
> Description: Copies data between files and tables
> Syntax:
> COPY [ BINARY ] table [ WITH OIDS ]
> FROM { 'filename' | stdin }
> [ [USING] DELIMITERS 'delimiter' ]
> [ WITH NULL AS 'null string' ]
> COPY [ BINARY ] table [ WITH OIDS ]
> TO { 'filename' | stdout }
> [ [USING] DELIMITERS 'delimiter' ]
> [ WITH NULL AS 'null string' ]
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Re: Postgresql bulk fast loader
@ 2001-07-12 23:18 Guy Fraser <guy@incentre.net>
parent: Mark Volpe <volpe.mark@epa.gov>
0 siblings, 0 replies; 5+ messages in thread
From: Guy Fraser @ 2001-07-12 23:18 UTC (permalink / raw)
To: ; +Cc: pgsql-hackers
Mark Volpe wrote:
>
> Avoid doing this with indexes on the table, though. I learned the hard way!
>
> Mark
>
> mlw wrote:
> >
> > Naomi Walker wrote:
> > >
> > > Does postgresql have any sort of fast bulk loader?
> >
> > It has a very cool SQL extension called COPY. Super fast.
> >
> > Command: COPY
> > Description: Copies data between files and tables
> > Syntax:
> > COPY [ BINARY ] table [ WITH OIDS ]
> > FROM { 'filename' | stdin }
> > [ [USING] DELIMITERS 'delimiter' ]
> > [ WITH NULL AS 'null string' ]
> > COPY [ BINARY ] table [ WITH OIDS ]
> > TO { 'filename' | stdout }
> > [ [USING] DELIMITERS 'delimiter' ]
> > [ WITH NULL AS 'null string' ]
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
Hi
On a daily basis I have an automated procedure that that bulk copies
information into a "holding" table. I scan for duplicates and put the
OID for the first unique record into a temporary table. Using the OID
and other information I do an INSERT with SELECT to move the unique
data into its appropriate table. Then I remove the unique records and
move the duplicates into a debugging table. After that I remove the
remaining records and drop the temporary tables. Once this is done I
vacuum the tables and regenerate the indexes.
This sounds complicated but by doing things in quick simple transactions
the database is able to run continuously without disruption. I am able
to import 30+ MB of data every day with only a small disruption when
updating the the summary tables.
Guy Fraser
--
There is a fine line between genius and lunacy, fear not, walk the
line with pride. Not all things will end up as you wanted, but you
will certainly discover things the meek and timid will miss out on.
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2001-07-12 23:18 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-07-11 00:05 Postgresql bulk fast loader Naomi Walker <nwalker@eldocomp.com>
2001-07-11 00:41 ` mlw <markw@mohawksoft.com>
2001-07-11 13:38 ` Mark Volpe <volpe.mark@epa.gov>
2001-07-12 23:18 ` Guy Fraser <guy@incentre.net>
2001-07-11 01:02 ` Bruce Momjian <pgman@candle.pha.pa.us>
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