News for hackers. Stuff that matters.
Re:PostgreSQL 10 Released by grzm on Thursday October 05, 2017 @11:32AM (#15409983)
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.

There are no comments on this comment yet.