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

Question

I want to do something like this in a PL/pgSQL function in Postgres 9.6:

INSERT INTO table1 (id, value) VALUES (1, 'a') ON CONFLICT DO NOTHING;
--- If the above statement didn't insert a new row
---   because id of 1 already existed, 
---   then run the following statements

INSERT INTO table2 (table1_id, value) VALUES (1, 'a');
UPDATE table3 set (table1_id, time) = (1, now());

However, I don't know how to determine whether the first INSERT actually inserted a new row, or whether the the ON CONFLICT DO NOTHING was done.

I could do a SELECT at the beginning of the function to see whether a record with id of 1 exists in table1 before running all the SQL statements, but this would lead to race conditions I think.

Answer 1

For a plpgsql function, use the special variable FOUND:

CREATE FUNCTION foo(int, text)
 RETURNS void AS
$$
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 surely don't want to update all rows in table3
   END IF;
END
$$

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 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 delete or update 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?