| 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: | Whole Thread | Raw Message | 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
| 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 == |