How to check if a row exists in a PostgreSQL store

问题:

I writing a stored procedure in postgres where I need to check if a row exists then act accordingly. something along the line.

IF SELECT * FROM foo WHERE x = 'abc' AND y = 'xyz' THEN
  -- do something here
ELSE 
  -- do something else
END;

I have googled a bit but got no good hits.

回答1:

Use PERFORM and the FOUND automatic variable:

PERFORM * FROM foo WHERE x = 'abc' AND y = 'xyz';
IF FOUND THEN
    ....
END IF;

This will succeed if one or more rows is returned. If you want to constrain the result to exactly one row use GET DIAGNOSTICS to get the row count, or use SELECT INTO to store the count(...) of the rows into a DECLAREd variable you then test. If it's an error to get no results, use SELECT INTO STRICT to require that exactly one row be obtained and stored into the target variable.

Beware of concurrency issues when doing anything like this. If you're attempting to write an upsert/merge function this approach will not work. See "why is upsert so complicated".

回答2:

Or even simpler with EXISTS:

IF EXISTS (SELECT 1 FROM foo WHERE x = 'abc' AND y = 'xyz') THEN
    ....
END IF;