Skip site navigation (1) Skip section navigation (2)

Re: Can COPY update or skip existing records?

From: "Rafael Domiciano" <rafael(dot)domiciano(at)gmail(dot)com>
To: "Glenn Gillen" <glenn(dot)gillen(at)gmail(dot)com>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: Can COPY update or skip existing records?
Date: 2008-10-01 01:07:09
Message-ID: 3a0028490809301807j59498370m1442d8f5867e9668@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-sql
Hi there,
The operation "on_duplicate_key_update" is in implementation on the new
version of Postgres :)
The only way (or, at least, the best way... I think) to do what you want is
using a temporary table... let's see:

/* Creating the structure of the first table (table_01)... You can do it the
way you like */
create table temp_01 as
(select * from table_01 limit 1);

TRUNCATE TABLE table_01;

/* COPY */
COPY temp_01 FROM '/tmp/table';

/* Insert the values */
insert into table_01 a
where not exists
(select 1 from temp_01 b
 where
  a.cod_serial = b.cod_serial)

/* Or you could do like this */
delete from temp_01 a
where exists
(select 1 from table_01 b
 where
  a.cod_serial = b.cod_serial)

I hope being helpful.

Best Regards,

Rafael Domiciano
Postgres DBA

2008/9/30 Glenn Gillen <glenn(dot)gillen(at)gmail(dot)com>

> Hey all,
>
> I've got a table with a unique constraint across a few fields which I
> need to regularly import a batch of data into. Is there a way to do it
> with COPY without getting conflicts on the unique contraint? I have no
> was of being certain that some of the data I'm trying to load isn't in
> the table already.
>
> Ideally I'd like it to operate like MySQL's on_duplicate_key_update
> option, but for now I'll suffice with just ignoring existing rows and
> proceeding with everything else.
>
> Thanks,
>
> --
> Glenn
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>

In response to

Responses

pgsql-sql by date

Next:From: Gurjeet SinghDate: 2008-10-01 02:27:31
Subject: Re: Can COPY update or skip existing records?
Previous:From: Oliveiros CristinaDate: 2008-09-30 17:01:23
Subject: Re: optimizing a query over tree-like structure

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group