Thread: copy from file and overwrite existing entries?

Hi,
I am really new to database administration, so forgive me if I overlook
something obvious ;>

I want to import data from a csv file into an existing, non-empty table.
My problem is the fact that I get an "ERROR:  duplicate key violates
unique constraint", because, as psql correctly grumbles, some entries
are already inside the tables. I would like to overwrite these existing
entries, as the data from the csv files is newer.
I have seen the UPDATE command, but I don't know how to combine that
with import from a file.

How can I accomplish that import? And what is the technically correct
description of my problem, so I can use a search engine next time?

Thanks in advance,
Mattes

am  27.06.2005, um 16:33:59 +0200 mailte Mattes Sarcander folgendes:
> Hi,
> I am really new to database administration, so forgive me if I overlook
> something obvious ;>
>
> I want to import data from a csv file into an existing, non-empty table.
> My problem is the fact that I get an "ERROR:  duplicate key violates
> unique constraint", because, as psql correctly grumbles, some entries
> are already inside the tables. I would like to overwrite these existing
> entries, as the data from the csv files is newer.

I suggest, use a separate import-table to copy the file in this table.
After you can work with usual SQL to delete old Records in the
destination table and insert the new records.


Regards, Andreas
--
Andreas Kretschmer    (Kontakt: siehe Header)
Heynitz:  035242/47212,      D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
 ===    Schollglas Unternehmensgruppe    ===

Andreas Kretschmer <akretschmer@despammed.com> wrote:

> > I want to import data from a csv file into an existing, non-empty
> > table. My problem is the fact that I get an "ERROR:  duplicate key
> > violates unique constraint", because, as psql correctly grumbles,
> > some entries are already inside the tables. I would like to
> > overwrite these existing entries, as the data from the csv files is
> > newer.
>
> I suggest, use a separate import-table to copy the file in this table.
> After you can work with usual SQL to delete old Records in the
> destination table and insert the new records.

Is there a way to automate this? The table is quite big, I expect some
few hundred duplicate entries.

On Mon, Jun 27, 2005 at 17:45:19 +0200,
  Mattes Sarcander <mtsrc@systemausfall.org> wrote:
> Andreas Kretschmer <akretschmer@despammed.com> wrote:
>
> > > I want to import data from a csv file into an existing, non-empty
> > > table. My problem is the fact that I get an "ERROR:  duplicate key
> > > violates unique constraint", because, as psql correctly grumbles,
> > > some entries are already inside the tables. I would like to
> > > overwrite these existing entries, as the data from the csv files is
> > > newer.
> >
> > I suggest, use a separate import-table to copy the file in this table.
> > After you can work with usual SQL to delete old Records in the
> > destination table and insert the new records.
>
> Is there a way to automate this? The table is quite big, I expect some
> few hundred duplicate entries.

It should be hard to write SQL to delete the duplicates from the original
table and then insert all of the records from the import table. If you
don't need to look at the duplicates by hand to decide which version to
keep, then there shouldn't be a problem with developing a script to
do the load.