Message-ID: <464F7A31.6020501@autoledgers.com.au>
Date: Sun, 20 May 2007 06:29:05 +0800
From: Paul Lambert <paul.lambert@autoledgers.com.au>
User-Agent: Thunderbird 1.5.0.10 (Windows/20070221)
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Re: ignoring primary key violations in COPY command
References: <B9F9AF34C9B5F743996B82EF46DF07CD03A6D4ED@BLR-EC-MBX04.wipro.com>
In-Reply-To: <B9F9AF34C9B5F743996B82EF46DF07CD03A6D4ED@BLR-EC-MBX04.wipro.com>
Content-Type: text/plain; charset=windows-1252; format=flowed
Content-Transfer-Encoding: 8bit

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