Re: INSERT WHERE NOT EXISTS - Mailing list pgsql-general

On Wednesday 25 June 2003 03:04 pm, scott.marlowe wrote:
> 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;

Yes, but I don't see how this is more efficient than what I said previously
(??)
Thanks though.
RDB


> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings

--
Reuben D. Budiardja
Department of Physics and Astronomy
The University of Tennessee, Knoxville, TN
-------------------------------------------------
/"\  ASCII Ribbon Campaign against HTML
\ /  email and proprietary format
 X   attachments.
/ \
-------------------------------------------------
Have you been used by Microsoft today?
Choose your life. Choose freedom.
Choose LINUX.
-------------------------------------------------