FAQ
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

Search Discussions

  • Andreas Kretschmer at Jun 27, 2005 at 2:51 pm

    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 ===
  • Mattes Sarcander at Jun 27, 2005 at 3:41 pm

    Andreas Kretschmer 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.
  • Bruno Wolff III at Jun 27, 2005 at 4:04 pm

    On Mon, Jun 27, 2005 at 17:45:19 +0200, Mattes Sarcander wrote:
    Andreas Kretschmer 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.

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-novice @
categoriespostgresql
postedJun 27, '05 at 2:30p
activeJun 27, '05 at 4:04p
posts4
users3
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2021 Grokbase