Message-ID: <3B4E3063.AB48238F@incentre.net>
Date: Thu, 12 Jul 2001 17:18:59 -0600
From: Guy Fraser <guy@incentre.net>
MIME-Version: 1.0
CC: PostgreSQL Development <pgsql-hackers@postgresql.org>
Subject: Re: Re: Postgresql bulk fast loader
References: <4.2.2.20010710170412.00a99820@logic1design.com>
	<3B4BA0B1.2C20EF74@mohawksoft.com> <3B4C56F1.5B46F924@epa.gov>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

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.