Re: ignoring primary key violations in COPY command

singh.srajendra@wipro.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