Insert without duplicates

From: John Burger <john(at)mitre(dot)org>
To: pgsql-general(at)postgresql(dot)org
Subject: Insert without duplicates
Date: 2005-04-01 21:19:00
Message-ID: a3d2592c866bd053408663698cf3d2d5@mitre.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi -

This has been covered elsewhere, but the typical answers seem to
involve using triggers, etc.

What's the best way to insert a row into a table providing it's not
already there? In my client (Python) program, I can do two separate
interactions with the server, the first a query:

select 1 from foo where x = 1 and y = 2;

and the second the actual insert, if the query returns nothing:

insert into foo (x, y) values (1, 2);

Or I can use EXCEPT:

insert into foo (x, y)
select 1, 2
except
select x, y from foo where x = 1 and y = 2;

Are there other variants? What's the "best" method (fastest, etc.).
The query planner will use the same plan in the second case as the
first, no? What if I had a handful of new rows I'd like to
(conditionally) insert - can I do them all in one statement somehow?
Remember, I don't want to use triggers or anything like that, just
standard SQL statements.

Thanks.

- John D. Burger
MITRE

Browse pgsql-general by date

  From Date Subject
Next Message Vivek Khera 2005-04-01 21:20:01 Re: [ANNOUNCE] == PostgreSQL Weekly News - April 01 2005 ==
Previous Message Bruce Momjian 2005-04-01 21:18:47 Re: [ANNOUNCE] == PostgreSQL Weekly News - April 01 2005 ==