From: Paul Lambert <paul.lambert@autoledgers.com.au>
To: pgsql-sql@postgresql.org
Subject: Re: ignoring primary key violations in COPY command
Date: Sun, 20 May 2007 06:29:05 +0800
Message-ID: <464F7A31.6020501@autoledgers.com.au> (raw)
In-Reply-To: <B9F9AF34C9B5F743996B82EF46DF07CD03A6D4ED@BLR-EC-MBX04.wipro.com>
References: <B9F9AF34C9B5F743996B82EF46DF07CD03A6D4ED@BLR-EC-MBX04.wipro.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: paul.lambert@autoledgers.com.au
Subject: Re: ignoring primary key violations in COPY command
In-Reply-To: <464F7A31.6020501@autoledgers.com.au>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox