Используйте INSERT ON CONFLICT DO YOUTHING RETURNING ошибочных строк

Предположим, у меня есть следующая таблица:

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

Мне нужен запрос, который будет вставлять теги, которые не существуют, и возвращать идентификаторы для всех запрошенных тегов. Рассмотрим следующее:

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

Результат этого запроса:

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

Мне нужно, чтобы на выходе было tag6.


postgresql postgresql-9.5
person nickbusted    schedule 08.02.2016    source источник
comment
Не связано, но знаете ли вы, что длина 255 для столбца varchar не имеет преимущества в производительности или хранении по сравнению с 254 или 300?   -  person a_horse_with_no_name    schedule 08.02.2016
comment
@a_horse_with_no_name объясните лучше... Я был здесь только по делу, но ваш комментарий пробудил во мне любопытство...   -  person jurhas    schedule 08.02.2016
comment
Длина 255 не имеет встроенной магии, если вы так думаете. Не существует секретной оптимизации, которая сделала бы столбец, объявленный как varchar(255), более эффективным, чем столбец, определенный как varchar(300). Каждый раз, когда я вижу это магическое число 255, я задаюсь вопросом, почему оно было выбрано — особенно с Postgres, где нет абсолютно никакой разницы в производительности между varchar(1) , varchar(78656)` и text (когда вы сохраняете только один символ). Вы должны рассматривать определение длины как бизнес-ограничение, а не как техническую вещь.   -  person a_horse_with_no_name    schedule 08.02.2016
comment
@a_horse_with_no_name Я думаю, что varchar(255) происходит от Oracle. (Кажется, я припоминаю, что меньшие столбцы varchar в Postgres не поджариваются)   -  person joop    schedule 08.02.2016
comment
@joop: Oracle никогда не оптимизировала varchar(255) (по крайней мере, с 8i). И решение о тосте (= сжатии) значения в Postgres принимается не на основе столбца definition, а на фактической длине хранящегося там значения.   -  person a_horse_with_no_name    schedule 08.02.2016
comment
Я понимаю. Я исправляюсь. Я вижу много varchar(255) в схемах, сгенерированных ORM (hibernate), которые, как я всегда подозревал, основаны на копировании/вставке шаблонов Oracle.   -  person joop    schedule 08.02.2016
comment
@a_horse_with_no_name 255 раньше была максимальной длиной varchar (дней MySQL 3.23 и т. д.)   -  person Pavel Francírek    schedule 14.03.2020

Ответы (2)


arrow_upward
10
arrow_downward

Немного многословно, но я не могу думать ни о чем другом:

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;

Внешний выбор из tags видит снимок таблицы, каким он был до вставки новых тегов. Третий столбец с константой предназначен только для проверки запроса, чтобы можно было определить, какие строки были вставлены, а какие нет.

person a_horse_with_no_name    schedule 08.02.2016
comment
Спасибо. я получаю syntax error at or near "id" LINE 3: ), inserted as (id, name) - person nickbusted; 08.02.2016
comment
Он делает то, что мне нужно. Большое спасибо! - person nickbusted; 08.02.2016

arrow_upward
1
arrow_downward

С этой таблицей:

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

Пока значения внутри запроса уникальны, обходной путь для этого:

INSERT INTO tags (name) 
VALUES ('tag10'), ('tag6'), ('tag11') 
ON CONFLICT DO UPDATE name = EXCLUDED.name RETURNING id, name;
person Jolbas    schedule 06.03.2018
comment
Я думаю, как часть MVCC, это обновление вызовет запись на диск, даже если значения, которые вы обновляете, одинаковы - что-то, о чем следует знать в производственной среде. - person kozyr; 28.05.2020