Ian Barwick wrote: > On Wednesday 25 June 2003 20:06, Reuben D. Budiardja wrote: > >>Hi, >>I am developing application with PHP as the front end, PGSQL as the >>backend. I am trying to figure out what's the best way to do this. >>I want to check if an entry already exists in the table. If it does, then I >>will do >>UPDATE tablename .... >> >>otherwise, I will do >>INSER INTO tablename... > > (...) > > >>I vaguely remember in Oracle, there is something like this: >> >>INSERT INTO mytable >>SELECT 'value1', 'value2' >> FROM dummy_table >> WHERE NOT EXISTS >> (SELECT NULL FROM mytable >> WHERE mycondition) >> >>This query will do INSERT, if there is not an entry already in the TABLE >>mytable that match the condition mycondition. Otherwise, the INSERT just >>fails and return 0 (without returning error), so I can check on that and do >>update instead. > > > This kind of query should work; just leave out the "FROM dummy_table" bit. > (in Oracle it would be "FROM dual").
I proposed that same solution 3 years ago. Tom shoots it down: http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&threadm=3A4D6116.1A613402%40mascari.com&rnum=1&prev=/groups%3Fq%3DMike%2BMascari%2BINSERT%2BNOT%2BEXISTS%26ie%3DUTF-8%26oe%3DUTF-8%26hl%3Den Reuben must be prepared for unique key violation, I'm afraid. And, despite the optimism in the link, we still don't have savepoints. :-( Mike Mascari [EMAIL PROTECTED] ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
