Re: ignoring primary key violations in COPY command

singh(dot)srajendra(at)wipro(dot)com wrote:
> Hi all,
>
> We are importing the data from the CSV file into the database using COPY
> command. But when the ‘primary key ‘ violation occurs , it stops
>
> Then and there and data is not updated into the tables. Also in the CSV
> file the number of columns is not fixed , its varies
>
> In number , it can range anywhere between 1 -22, I need to figure out a
> way such that I need to update only those columns which are present
>
> In the CSV file . This of course I would like to accomplish using COPY
> command , Please let me know if at all this is possible in postgresql 8.1
>
> /Thanks and regards,/
>
> Rajendra Singh
>

In my opinion your best bet in terms of getting around the primary key
violation is to create a temporary table without a primary key, copy
your data into that table, then do a select into your main table from
that table.

Eg.. I do the following:

CREATE TABLE creditors_temp_load AS SELECT * FROM creditors WHERE 1=0;
TRUNCATE TABLE creditors;
COPY creditors_temp_load FROM 'c:/temp/autodrs_creditors.txt' WITH
DELIMITER AS '^' QUOTE '\f' CSV HEADER;
INSERT INTO creditors (SELECT DISTINCT ON (dealer_id,supplier_no) * FROM
creditors_temp_load WHERE (dealer_id,supplier_no) is not null);

The first statement creates a copy of the 'creditors' table without any
records (none in the creditors table have 1 equal to zero)
The second copies the data from the file into the temp table.
Finally an insert into the 'creditors' table is done by a select
distinct on the temp table where the two fields listed are the primary
key for that table.

I don't believe there is any way of getting around not having all the
fields present - copy expects to find a match between fields in the file
and fields in the destination table. If your record length in the load
file is going to vary you may need to consider writing a program to read
the data from the file and load it in.

Regards,
Paul.

--
Paul Lambert
Database Administrator
AutoLedgers