PL/pgSQL checking if a row exists

by

Simpler, shorter, faster: EXISTS.

IF EXISTS (SELECT 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 scans all (qualifying) rows regardless. Makes a big 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.

Only the existence of at least one qualifying row matters. The SELECT list can be empty – in fact, that’s shortest and cheapest. (Some other RDBMS don’t allow an empty SELECT list on principal.)

Improved with @a_horse_with_no_name’s comments.

More Related Contents:

  1. Postgres function returning table not returning data in columns
  2. How to perform a select query in a DO block?
  3. Select rows which are not present in other table
  4. Refactor a PL/pgSQL function to return the output of various SELECT queries
  5. Is SELECT or INSERT in a function prone to race conditions?
  6. How to return result of a SELECT inside a function in PostgreSQL?
  7. Pass multiple values in single parameter
  8. GROUP BY and aggregate sequential numeric values
  9. Constraint defined DEFERRABLE INITIALLY IMMEDIATE is still DEFERRED?
  10. PostgreSQL parameterized Order By / Limit in table function
  11. Return multiple fields as a record in PostgreSQL with PL/pgSQL
  12. PostgreSQL IF statement
  13. Call a set-returning function with an array argument multiple times
  14. Compute percents from SUM() in the same SELECT sql query
  15. Postgres analogue to CROSS APPLY in SQL Server
  16. Postgres Dynamic Query Function
  17. Return setof record (virtual table) from function
  18. Window Functions or Common Table Expressions: count previous rows within range
  19. How to pass custom type array to Postgres function
  20. How to find a table having a specific column in postgresql
  21. DROP FUNCTION without knowing the number/type of parameters?
  22. Return multiple columns and rows from a function PostgreSQL instead of record
  23. COPY with dynamic file name
  24. Check if NULL exists in Postgres array
  25. pseudo_encrypt() function in plpgsql that takes bigint
  26. How to use SQL LIKE condition with multiple values in PostgreSQL?
  27. How to create a new database with the hstore extension already installed?
  28. Function with SQL query has no destination for result data
  29. Postgres trigger after insert accessing NEW
  30. PostgreSQL – dynamic value as table name [duplicate]

Leave a Comment