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
--
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.
-------------------------------------------------
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
