Re: copy table from file: with row replacement?

From: Bruce Momjian <bruce(at)momjian(dot)us>
To: Michael Enke <michael(dot)enke(at)wincor-nixdorf(dot)com>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: copy table from file: with row replacement?
Date: 2007-01-12 15:44:27
Message-ID: 200701121544.l0CFiRF16418@momjian.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Michael Enke wrote:
> Hello all,
> I have a feature request as I think it is not possible with the actual version:
>
> I want to load huge amount of data and I know that COPY is much faster than doing inserts.
> But in my case I have an already filled table and rows (not all, only partly) from this table
> should be replaced. The table has a primary key for one column.
> If I do a COPY table FROM file and the key value already exists, postgresql tells me
> that the import is not possible because of the violation of the PK.
>
> If postgres is aware of such a violation, couldn't there be an option to the COPY command
> to delete such existing rows so that a COPY table FROM file will never generate a PK violation message
> but replaces existing rows?
>
> If this is not possible, would it be the next fastes solution to create a before trigger and to
> delete rows in this trigger? Or is this not different from issuing for every line an insert
> and if this fails (because of the PK) than an update?

I would just COPY into another table, remove any duplicates by joining
the two tables, and then do a INSERT INTO ... SELECT.

--
Bruce Momjian bruce(at)momjian(dot)us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Magnus Hagander 2007-01-12 16:06:09 Re: [HACKERS] Checkpoint request failed on version 8.2.1.
Previous Message Tom Lane 2007-01-12 15:21:09 Re: [HACKERS] Checkpoint request failed on version 8.2.1.