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