From: | Benjamin Jury <benjamin(dot)jury(at)mpuk(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | FW: INSERT WHERE NOT EXISTS |
Date: | 2003-06-26 09:31:11 |
Message-ID: | 24DC688F52AAD611B60900096BB0B440015D82B6@chapar.mpuk.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
> // 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.
Why not use plpgsql?
CREATE FUNCTION a_test(int4) RETURNS int AS '
DECLARE
totest int;
BEGIN
SELECT INTO totest <ID> FROM <table> WHERE <ID> = $1;
IF totest IS null THEN
-- do insert.
return 1;
ELSE
-- do update.
return 0;
END IF;
END;
' language 'plpgsql';
For efficiency make sure ID is a index...
From | Date | Subject | |
---|---|---|---|
Next Message | Matt Browne | 2003-06-26 11:00:07 | Foreign keys |
Previous Message | Mark Kirkwood | 2003-06-26 08:06:59 | Re: Question regarding performance (large objects involved) |