Re: COPY from .csv File and Remove Duplicates

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