SyntaxFix

[sql] PL/pgSQL checking if a row exists

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.

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?