Date: Wed, 4 Jan 2012 10:08:38 +0100
From: Andreas Kretschmer <akretschmer@spamfence.net>
To: pgsql-sql@postgresql.org
Subject: Re: ignore unique violation OR check row exists
Message-ID: <20120104090838.GA6039@tux>
References: <1325624070446-5117916.post@n5.nabble.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <1325624070446-5117916.post@n5.nabble.com>
User-Agent: Mutt/1.5.18 (2008-05-17)

rverghese <riyav@hotmail.com> wrote:

> I want to insert a bunch of records and not do anything if the record already
> exists. So the 2 options I considered are 1) check if row exists or insert
> and 2) ignore the unique violation on insert if row exists. 
> Any opinions on whether it is faster to INSERT and then catch the UNIQUE
> VIOLATION exception and ignore it in plpgsql  versus check if row exists and
> INSERT if it doesn't. 
> I can't seem to ignore the UNIQUE VIOLATION exception via php, since it is a
> plpgsql command, so if I have to do the check and insert, alternatively i
> have a function that tries to insert and then ignores the violation. I was
> wondering if one way was better than the other.
> Thanks

If i where you i would use an extra table. Insert all new records into
this table (i called it src) and select all records from src into dest
(the destination table) which are not in dest. See my example:

test=# select * from dest ;
 i
---
 1
 2
 3
 4
 5
(5 rows)

Time: 0,148 ms
test=*# select * from src ;
 i
---
 4
 5
 6
 7
 8
 9
(6 rows)

Time: 0,200 ms
test=*# insert into dest select src.* from src left join dest using (i)
where dest.i is null;
INSERT 0 4
Time: 0,464 ms
test=*# select * from dest;
 i
---
 1
 2
 3
 4
 5
 6
 7
 8
 9
(9 rows)


I think, that's the fastest way.


Andreas
-- 
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°