From: | "scott(dot)marlowe" <scott(dot)marlowe(at)ihs(dot)com> |
---|---|
To: | "Reuben D(dot) Budiardja" <techlist(at)voyager(dot)phys(dot)utk(dot)edu> |
Cc: | <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: INSERT WHERE NOT EXISTS |
Date: | 2003-06-25 19:04:40 |
Message-ID: | Pine.LNX.4.33.0306251303270.30939-100000@css120.ihs.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Jeff | 2003-06-25 19:23:16 | Re: [GENERAL] Physical Database Configuration |
Previous Message | AgentM | 2003-06-25 18:48:49 | Re: [GENERAL] Physical Database Configuration |