Re: INSERT WHERE NOT EXISTS

Just wrap it in a transaction:

begin;
select * from table where somefield='somevalue';
(in php code)
if pg_num_rows>1...
update table set field=value where somefield=somevalue;
else
insert into table (field) values (value);
commit;

On Wed, 25 Jun 2003, 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...
>
> What's the best way to do that? I can of course check first, and then put the
> login in PHP code, eg:
>
> // check if entry already exists
> SELECT COUNT(*) FROM tablename WHERE [cond]
> ..
> if($count >0)
>   UPDATE
> else
>   INSERT
>
> but this will double the hit to the database server, because for every
> operation I need to do SELECT COUNT(*) first. The data itself is not a lot,
> and the condition is not complex, but the hitting frequency is a lot.
>
> 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 is especially useful in my case because about most of the time the INSERT
> will succeed, and thus will reduce the hit frequency to the DB server from
> PHP by probably a factor of 1.5 or so.
>
> Is there anything like that with PostgreSQL? I looked the docs and googled but
> haven't found anything.
>
> Anyhelp is greatly appreciated. Thanks.
>
> RDB
>