Thread: Happy Anniversary
I suppose few people have remembered that today is what could be considered the 5th anniversary of the PostgreSQL project. Cheers for another five years! http://www.ca.postgresql.org/mhonarc/pgsql-hackers/1999-10/msg00552.html -- Peter Eisentraut peter_e@gmx.net http://funkturm.homeip.net/~peter
> I suppose few people have remembered that today is what could be > considered the 5th anniversary of the PostgreSQL project. Cheers for > another five years! > > > http://www.ca.postgresql.org/mhonarc/pgsql-hackers/1999-10/msg00552.html Good catch! Yes, you are right. -- 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, Pennsylvania19026
Does postgresql have any sort of fast bulk loader? -- Naomi Walker Chief Information Officer Eldorado Computing, Inc. 602-604-3100 ext 242
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
'nullstring' ]
COPY [ BINARY ] table [ WITH OIDS ] TO { 'filename' | stdout } [ [USING] DELIMITERS 'delimiter' ] [ WITH NULL AS
'nullstring' ]
> 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, Pennsylvania19026
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
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.