PL/pgSQL checking if a row exists
Solution 1
Simpler, shorter, faster: EXISTS
.
IF EXISTS (SELECT 1 FROM people p WHERE p.person_id = my_person_id) THEN
-- do something
END IF;
The query planner can stop at the first row found - as opposed to count()
, which will scan all (matching) rows regardless. Makes a difference with big tables. The difference is small for a condition on a unique column: only one row qualifies and there is an index to look it up quickly.
Improved with input from @a_horse_with_no_name in the comments.
You can just use an empty SELECT
list:
IF EXISTS (SELECT FROM people p WHERE p.person_id = my_person_id) THEN ...
The SELECT
list has no influence on the result of EXISTS
. Only the existence of at least one qualifying row matters.
Solution 2
Use count(*)
declare
cnt integer;
begin
SELECT count(*) INTO cnt
FROM people
WHERE person_id = my_person_id;
IF cnt > 0 THEN
-- Do something
END IF;
Edit (for the downvoter who didn't read the statement and others who might be doing something similar)
The solution is only effective because there is a where clause on a column (and the name of the column suggests that its the primary key - so the where clause is highly effective)
Because of that where
clause there is no need to use a LIMIT or something else to test the presence of a row that is identified by its primary key. It is an effective way to test this.
nnyby
Updated on March 11, 2021Comments
-
nnyby about 3 years
I'm writing a function in PL/pgSQL, and I'm looking for the simplest way to check if a row exists.
Right now I'm SELECTing aninteger
into aboolean
, which doesn't really work. I'm not experienced with PL/pgSQL enough yet to know the best way of doing this.Here's part of my function:
DECLARE person_exists boolean; BEGIN person_exists := FALSE; SELECT "person_id" INTO person_exists FROM "people" p WHERE p.person_id = my_person_id LIMIT 1; IF person_exists THEN -- Do something END IF; END; $$ LANGUAGE plpgsql;
Update - I'm doing something like this for now:
DECLARE person_exists integer; BEGIN person_exists := 0; SELECT count("person_id") INTO person_exists FROM "people" p WHERE p.person_id = my_person_id LIMIT 1; IF person_exists < 1 THEN -- Do something END IF;