Re: 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.