Re: COPY from .csv File and Remove Duplicates - Mailing list pgsql-general

From Craig Ringer
Subject Re: COPY from .csv File and Remove Duplicates
Date
Msg-id 4E448C8F.3070109@ringerc.id.au
Whole thread Raw
In response to COPY from .csv File and Remove Duplicates  (Rich Shepard <rshepard@appl-ecosys.com>)
List pgsql-general
On 12/08/2011 7:13 AM, Rich Shepard wrote:

> I have a .csv file of approximately 10k rows to copy into this table. My
> two questions which have not been answered by reference to my postgres
> reference book or Google searches are:
>
> 1) Will the sequence automatically add the nextval() to each new record as
> the copy command runs?

No, AFAIK COPY input needs to match the table structure and can't have
default fields etc.

> 2) Many of these rows almost certainly are already in the table. I would
> like to remove duplicates either during the COPY command or immediately
> after. I'm considering copying the new data into a clone of the table then
> running a SELECT to add only those rows in the new cloned table to the
> existing table.

Rather than removing them after, I'd recommend COPYing into a temporary
staging table, then doing an

INSERT INTO realtable SELECT FROM stagingtable LEFT OUTER JOIN realtable
ON (conditions) WHERE realtable.primarykey IS NULL;

... where "conditions" are whatever rules you use to decide that a row
in the real table is the same as a row in the staging table.

In other words: Only insert a row into the final destination table if it
does not already exist in the final destination table.

--
Craig Ringer

pgsql-general by date:

Previous
From: Ondrej Ivanič
Date:
Subject: Re: Postgres on SSD
Next
From: "David Johnston"
Date:
Subject: Re: COPY from .csv File and Remove Duplicates