How to check if a row exists in a PostgreSQL stored procedure?

Question

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.

Craig Ringer · Accepted Answer · 2012-10-11 03:30:23Z

30

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".

edited Oct 11, 2012 at 3:30

answered Oct 11, 2012 at 3:23

Craig Ringer

329k84 gold badges742 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

ams Over a year ago

IS FOUND some implicit variable ? tried to find where it was mentioned in the docs but could not, its too generic a term while searching.

Craig Ringer Over a year ago

@ams from the linked page above, "Also, the special variable FOUND is set to true if the query produced at least one row, or false if it produced no rows (see Section 39.5.5)." . See 39.5.5. Obtaining the Result Status; it's also mentioned elsewhere throughout the "basic statements" page on PL/PgSQL.

Erwin Brandstetter · Accepted Answer · 2021-01-22 18:07:40Z

30

Or even simpler with EXISTS:

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

See:

PL/pgSQL checking if a row exists

edited Jan 22, 2021 at 18:07

answered Oct 11, 2012 at 5:54

Erwin Brandstetter

668k160 gold badges1.2k silver badges1.3k bronze badges

Collectives™ on Stack Overflow

How to check if a row exists in a PostgreSQL stored procedure?

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related