Use INSERT ... ON CONFLICT DO NOTHING RETURNING failed rows

Question

Suppose I have the following table:

CREATE TABLE tags (
    id int PK,
    name varchar(255),
    CONSTRAINT name_unique UNIQUE(name)
)

I need a query that will insert tags that do not exists and return ids for all requested tags. Consider the following:

INSERT INTO tags (name) values ('tag10'), ('tag6'), ('tag11') ON CONFLICT DO NOTHING returning id, name

The output of this query is:

+---------------+
|  id   |  name |
|---------------|
|  208  | tag10 |
|---------------|
|  209  | tag11 |
+---------------+

What I need is to have tag6 in the output.

Unrelated, but: are you aware that the length 255 for a varchar column has no performance or storage advantage over 254 or 300? — , Feb 08 '16 at 08:55
@a_horse_with_no_name explain it better... I was here just for my business but your comment awakes my curiosity... — jurhas, Feb 08 '16 at 10:08
The length 255 has no "magic" built in if that is what you think. There is no secret optimization that would make a column declared as `varchar(255)` more "efficient" than one defined as `varchar(300)`. Every time I see that "magic number" 255 I question _why_ this has been chosen - **especially** with Postgres where there is absolutely no difference in _performance_ between `varchar(1)`, varchar(78656)` and `text` (when you only store a single character). You need to see the length definition as a business constraint, not as a technical "thing" — , Feb 08 '16 at 10:13
@a_horse_with_no_name I think the `varchar(255)` stems from Oracle. (I do seem to recall that smaller varchar columns in Postgres are not toasted) — joop, Feb 08 '16 at 10:17
@joop: Oracle never had any optimization regarding `varchar(255)` (at least not since 8i). And the decision if a value is toasted (=compressed) in Postgres is not decided based on the column _definition_ but on the actual length of the value stored in there — , Feb 08 '16 at 10:31
I see. I stand corrected. I see a lot of varchar(255) in ORM-generated schemas (hibernate), which I always suspected to be based on copy/pasted Oracle templates. — joop, Feb 08 '16 at 10:50
@a_horse_with_no_name 255 used to be maximum length of varchar (days of MySQL 3.23 etc) — Pavel Francírek, Mar 14 '20 at 19:06

score 10 · Accepted Answer · 2016-02-08T09:15:57.203

10

A bit verbose, but I can't think of anything else:

with all_tags (name) as (
  values ('tag10'), ('tag6'), ('tag11')
), inserted (id, name) as (
   INSERT INTO tags (name)
   select name 
   from all_tags
   ON CONFLICT DO NOTHING 
   returning id, name
)
select t.id, t.name, 'already there'
from tags t
  join all_tags at on at.name = t.name
union all
select id, name, 'inserted'
from inserted;

The outer select from tags sees the snapshot of the table as it was before the new tags were inserted. The third column with the constant is only there to test the query so that one can identify which rows were inserted and which not.

edited Feb 08 '16 at 09:15

answered Feb 08 '16 at 08:54

Thanks. I get `syntax error at or near "id" LINE 3: ), inserted as (id, name) ` – nickbusted Feb 08 '16 at 09:14
It does what I need. Thanks a lot! – nickbusted Feb 08 '16 at 09:19

score 3 · Answer 2 · answered Mar 06 '18 at 13:16

3

With this table:

CREATE TABLE tags (
    id serial PRIMARY KEY,
    name text UNIQUE
);

As long as the values inside the query is unique a workaround for this is:

INSERT INTO tags (name) 
VALUES ('tag10'), ('tag6'), ('tag11') 
ON CONFLICT DO UPDATE name = EXCLUDED.name RETURNING id, name;

answered Mar 06 '18 at 13:16

Jolbas

757
5
15

1

I think as part of MVCC, this update will trigger an disk write even though the values you're updating are the same - something to be aware in the production environment. – kozyr May 28 '20 at 00:09

Use INSERT ... ON CONFLICT DO NOTHING RETURNING failed rows

2 Answers2

Linked