3 ms·
If I understand what you're looking for, you can likely accomplish this using a WITH expression, something along the lines of (untested): WITH input_
by grzm 9y ago
If I understand what you're looking for, you can likely accomplish this using a WITH expression, something along the lines of (untested):
WITH
input_rows (name) AS
(VALUES ('a'), ('b'), ('c')),
insert_rows AS
(INSERT INTO entities (name)
SELECT ir.name
FROM input_rows AS ir
ON CONFLICT DO NOTHING
RETURNING id, name),
-- returned when insert was successful
SELECT 'new' AS src, i.id, i.name
FROM insert_rows AS i
UNION ALL
-- return when there's a conflict
SELECT 'existing' AS src, e.id, e.name
FROM entities AS e
JOIN input_rows USING (name);
As for the sequence incrementing, that's the nature of sequences: it's a trade-off between getting fast, unique values versus the overhead required tracking whether the id is used.