From: | Michael Enke <michael(dot)enke(at)wincor-nixdorf(dot)com> |
---|---|
To: | |
Cc: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: copy table from file: with row replacement? |
Date: | 2007-01-12 16:38:28 |
Message-ID: | 45A7B984.6050908@wincor-nixdorf.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
This works for small amount of data. But for large amount of data
the join takes a lot of time.
Regards,
Michael
Bruce Momjian wrote:
> 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.
>
From | Date | Subject | |
---|---|---|---|
Next Message | Bruce Momjian | 2007-01-12 16:44:39 | Re: [HACKERS] [PATCHES] Patch to log usage of |
Previous Message | Tom Lane | 2007-01-12 16:35:25 | Re: [HACKERS] Checkpoint request failed on version 8.2.1. |