Pl/Pgsql Checking If a Row Exists

PL/pgSQL checking if a row exists

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

Fastest check if row exists in PostgreSQL

Use the EXISTS key word for TRUE / FALSE return:

select exists(select 1 from contact where id=12)

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

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

Check to see if a record exists postgres function

also, your dynamic string is bad, better use:

select format('select exists from %I.%I where %I = %L',schema_name,table_name,field_name, field_value) into _sql_string;

also, you realize your _field_value has no check for data type?..

so in short, it could be smth similar to:

db=# CREATE or replace FUNCTION records_exist(schema_name VARCHAR(255), table_name VARCHAR(255), field_name VARCHAR(255), field_value VARCHAR(255))
  RETURNS BOOLEAN
LANGUAGE plpgsql
AS $$
DECLARE
 _sql text;
 _b boolean;
BEGIN
 _sql := format('select exists (select null from %I.%I where %I = %L)',schema_name,table_name,field_name, field_value);
 execute _sql into _b;
 return _b;
END
$$;
CREATE FUNCTION
Time: 10.680 ms
db=# select * from records_exist('pg_catalog','pg_database','datname','postgres'); records_exist
---------------
 t
(1 row)

Time: 59.472 ms

Check if a row exists or not in postgresql

select
  case when exists (select true from table_name where table_column=?)
    then 'true'
    else 'false'
  end;

But it would be better to just return boolean instead of string:

select exists (select true from table_name where table_column=?);

Run SQL statements in PL/pgSQL only if a row doesn't exist

For a PL/pgSQL function, use the special variable FOUND:

CREATE FUNCTION foo(int, text)
  RETURNS void
  LANGUAGE plpgsql AS
$func$
BEGIN
   INSERT INTO table1 (id, value)
   VALUES ($1, $2)
   ON CONFLICT DO NOTHING;

   IF NOT FOUND THEN
      INSERT INTO table2 (table1_id, value)
      VALUES ($1, $2);
      UPDATE table3
      SET   (table1_id, time)
          = ($1       , now())
      WHERE  ???;  -- you don't want to update all rows in table3?
   END IF;
END
$func$;

Call:

SELECT foo(1, 'a');

FOUND is set to false if the INSERT does not actually insert any rows.

The manual about the ON CONFLICT Clause:

ON CONFLICT DO NOTHING simply avoids inserting a row as its
alternative action.

The manual about Obtaining the Result Status

UPDATE, INSERT, and DELETE statements set FOUND true if at least one
row is affected, false if no row is affected.

To be clear, this runs the later statements if a row in table1 does already exist, so the new row is not inserted. (Like you requested, but contrary to your question title.)

If you just want to check whether a row exists:

• PostgreSQL IF statement

Race condition?

If subsequent commands in the same transaction depend on the (yet unlocked) existing row in table1 (with a FK for instance), you'll want to lock it to defend against concurrent transactions deleting or updating it in the meantime. One way to do this: instead of DO NOTHING use DO UPDATE, but do not actually update the row. The row is still locked:

INSERT INTO table1 AS t (id, value)
VALUES ($1, $2)
ON     CONFLICT (id) DO UPDATE  -- specify unique column(s) or constraint / index
SET    id = t.id WHERE false;   -- never executed, but locks the row

Obviously, if you can rule out concurrent transactions that might write to the same row in a conflicting manner, then the problem does not exist.

Detailed explanation:

• How to include excluded rows in RETURNING from INSERT ... ON CONFLICT
• Is SELECT or INSERT in a function prone to race conditions?

How to check if values exists in a table - postgres PLPGSQL

Your logic seems unnecessarily complex. You can just check if any user doesn't exist using NOT EXISTS:

SELECT 1
FROM UNNEST(users) user_to_check
WHERE NOT EXISTS (SELECT 1 FROM users u WHERE u.id = user_to_check)

---

Related Topics