Insert if names do not exist and return ids if exist 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.
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&lt;>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?