Insert if names do not exist and return ids if exist

Question

Suppose I have an array of string "a","b","c","d","e".

CREATE TABLE foo (
  id   int   GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  name text  UNIQUE
);
INSERT INTO foo (name) VALUES ('a'), ('d');  -- assigned IDs 1 and 2

What will be the query to pass an array of strings and insert each if that name doesn't exist and return all IDs for the given array? In this example, it should return 1,2,3,4,5 as an output of SQL execution.

Answer 1

It's the recurring problem of INSERT or SELECT, related to the common UPSERT, but not the same. INSERT ... ON CONFLICT ... DO ... (commonly called UPSERT), introduced with Postgres 9.5, is instrumental in any case.

Assuming the simple case without concurrent write load on the table:

CREATE TABLE foo (
  id   int   GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  name text  UNIQUE
);
INSERT INTO foo (name) VALUES ('a'), ('d');  -- assigned IDs 1 and 2

WITH input(name) AS (SELECT unnest('{a,b,c,d,e}'::text[]))  -- input array once
, ins AS (
   INSERT INTO foo(name) 
   TABLE  input
   ON CONFLICT (name) DO NOTHING
   RETURNING id
   )
SELECT f.id
FROM   input i
JOIN   foo   f USING (name)
UNION  ALL
TABLE  ins;

| id |
| -: |
|  1 |
|  2 |
|  4 |
|  5 |
|  7 |

db<>fiddle here

Note the missing serial IDs 3 and 6. A side effect of UPSERT is that conflicting rows burn a serial number since default values are fetched before checking for conflicts, and sequences are never set back. That should be irrelevant, since gaps in serial numbers are to be expected at all times. Meaning, you cannot rely on a result 1,2,3,4,5 for the given example (and assumed setup).

Detailed explanation and alternative solutions - especially for concurrent write load:

• Is SELECT or INSERT in a function prone to race conditions?

• How to use RETURNING with ON CONFLICT in PostgreSQL?

Answer 2

You could put the INSERT in a CTE RETURNING id and SELECT from that CTE and UNION ALL a SELECT fetching all the matching ids already in foo.

WITH cte
AS (
INSERT INTO foo
            (name)
            SELECT a_name
                   FROM unnest(ARRAY['a', 'b', 'c', 'd', 'e']) a_name
            WHERE NOT EXISTS (SELECT *
                                     FROM foo
                                     WHERE name = a_name)
            RETURNING id
)
SELECT id
       FROM cte
UNION ALL
SELECT id
       FROM foo
            INNER JOIN unnest(ARRAY['a', 'b', 'c', 'd', 'e']) a_name
                       ON name = a_name;

SQL Fiddle