inserting only new rows from csv file

On Wed, Oct 1, 2008 at 3:16 PM, Seb <spluque@gmail.com> wrote:

Hi,

I've seen some approaches to this problem, but I suspect there are
better ways.

A csv file is produced nightly, with the same table structure as a table
in a PostgreSQL database. The file grows over time as new records are
added every day. However, the rows in the file may not be sorted the
same way every day.

I need to isolate the new rows and insert them into the database table.
Before I start writing a script to do this, I need to decide whether to
do the comparison between the database table and the csv file: a) within
the database, or b) COPY'ing the table to a file and then do the
comparison. I'm not very experienced with SQL, so am more comfortable
with the latter option using plain shell and awk. Which route is best?
If a), I'd appreciate some SQL code to do it. Thanks.

I'd load them into a staging table, and use a query (either a
subselect or a left join where null type query) to load them into the
master table.

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]On Behalf Of Scott Marlowe
Sent: Thursday, 2 October 2008 7:39 AM
To: Seb
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] inserting only new rows from csv file

On Wed, Oct 1, 2008 at 3:16 PM, Seb <spluque@gmail.com> wrote:

Hi,

I've seen some approaches to this problem, but I suspect there are
better ways.

A csv file is produced nightly, with the same table

structure as a table

in a PostgreSQL database. The file grows over time as new

records are

added every day. However, the rows in the file may not be

sorted the

same way every day.

I need to isolate the new rows and insert them into the

database table.

Before I start writing a script to do this, I need to

decide whether to

do the comparison between the database table and the csv

file: a) within

the database, or b) COPY'ing the table to a file and then do the
comparison. I'm not very experienced with SQL, so am more

comfortable

with the latter option using plain shell and awk. Which

route is best?

If a), I'd appreciate some SQL code to do it. Thanks.

I'd load them into a staging table, and use a query (either a
subselect or a left join where null type query) to load them into the
master table.

I solved the same problem this way.
csv file processed by gawk script to produce SQL script with inserts etc. Then pass
the SQL file to psql.

This is what I have in my BEGIN line of my gawk script leaving out appropriate print statements for clarity.

CREATE TEMPORARY TABLE tempjobdetails( LIKE jobdetails INCLUDING DEFAULTS );

--Do load from csv into tempjobdetails

This is in my END line of the gawk scrip ditto the print statements.

insert into jobdetails
( select * from tempjobdetails
where
( tempjobdetails.jobnum, tempjobdetails.opnum )
not in
( select
jobdetails.jobnum, jobdetails.opnum from jobdetails
)
);

Hope this helps.

Allan

The material contained in this email may be confidential, privileged or copyrighted. If you are not the intended recipient, use, disclosure or copying of this information is prohibited. If you have received this document in error, please advise the sender and delete the document. Neither OneSteel nor the sender accept responsibility for any viruses contained in this email or any attachments.