SyntaxFix

[sql] PL/pgSQL checking if a row exists

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. Hardly matters with a condition on a unique column - only one row qualifies anyway (and there is an index to look it up quickly).

Improved with input from @a_horse_with_no_name in the comments below.

You could even use an empty SELECT list:

IF EXISTS (SELECT FROM people p WHERE p.person_id = my_person_id) THEN ...

Since the SELECT list is not relevant to the outcome of EXISTS. Only the existence of at least one qualifying row matters.

Examples related to sql

• Passing multiple values for same variable in stored procedure • SQL permissions for roles • Generic XSLT Search and Replace template • Access And/Or exclusions • Pyspark: Filter dataframe based on multiple conditions • Subtracting 1 day from a timestamp date • PYODBC--Data source name not found and no default driver specified • select rows in sql with latest date for each ID repeated multiple times • ALTER TABLE DROP COLUMN failed because one or more objects access this column • Create Local SQL Server database

Examples related to postgresql

• Subtracting 1 day from a timestamp date • pgadmin4 : postgresql application server could not be contacted. • Psql could not connect to server: No such file or directory, 5432 error? • How to persist data in a dockerized postgres database using volumes • input file appears to be a text format dump. Please use psql • Postgres: check if array field contains value? • Add timestamp column with default NOW() for new rows only • Can't connect to Postgresql on port 5432 • How to insert current datetime in postgresql insert query • Connecting to Postgresql in a docker container from outside

Examples related to plpgsql

• No function matches the given name and argument types • Postgres FOR LOOP • Return zero if no record is found • PostgreSQL - SQL state: 42601 syntax error • Store query result in a variable using in PL/pgSQL • PL/pgSQL checking if a row exists • PostgreSQL IF statement • Postgresql, update if row with some unique value exists, else insert • Loop over array dimension in plpgsql • How to return result of a SELECT inside a function in PostgreSQL?

Examples related to postgresql-9.1

• Postgresql SQL: How check boolean field with null and True,False Value? • must appear in the GROUP BY clause or be used in an aggregate function • postgres, ubuntu how to restart service on startup? get stuck on clustering after instance reboot • List tables in a PostgreSQL schema • PostgreSQL ERROR: canceling statement due to conflict with recovery • How do I convert an integer to string as part of a PostgreSQL query? • ALTER TABLE, set null in not null column, PostgreSQL 9.1 • Strange PostgreSQL "value too long for type character varying(500)" • PostgreSQL next value of the sequences? • Change type of varchar field to integer: "cannot be cast automatically to type integer"

Examples related to exists

• Select rows which are not present in other table • How to fix "Only one expression can be specified in the select list when the subquery is not introduced with EXISTS" error? • Check if record exists from controller in Rails • sql: check if entry in table A exists in table B • How to exclude records with certain values in sql select • SQL - IF EXISTS UPDATE ELSE INSERT INTO • Linq select objects in list where exists IN (A,B,C) • PL/pgSQL checking if a row exists • How to Check if value exists in a MySQL database • MongoDB: How to query for records where field is null or not set?