Можно ли использовать INSERT [] ON CONFLICT для нарушений внешнего ключа?

Дано

=> select * from referenced;
 referenced_id | name  
---------------+-------
             1 | one
             2 | two
             3 | three

а также

=> select * from entries;
 entry_id | referenced_id |      name      
----------+---------------+------------------
        1 |             3 | references three

где referenced_id и entry_id — первичные ключи.

Мне нужен оператор вставки для entries, который пропускает вставку, если либо entry_id уже существует, либо указанный элемент не существует. Первое легко сделать:

INSERT INTO entries
VALUES (1, 2, 'references two')
ON CONFLICT (entry_id) DO NOTHING;

Можно ли проверить наличие внешнего ключа и здесь?


postgresql sql sql-insert postgresql-9.5 upsert
person peterwimsey    schedule 16.03.2016    source источник

Ответы (1)


arrow_upward
13
arrow_downward

Да, присоедините свои входные строки к таблице, на которую указывает ссылка, тем самым удалив строки без совпадения в столбце FK:

INSERT INTO entries(entry_id, referenced_id, name)
SELECT val.entry_id, val.referenced_id, val.name
FROM  (
  VALUES (1, 2, 'references two')
         -- more?
  ) val (entry_id, referenced_id, name)
JOIN   referenced USING (referenced_id)  -- drop rows without matching FK
ON     CONFLICT (entry_id) DO NOTHING;   -- drop rows with duplicate id

Сам UPSERT (INSERT ... ON CONFLICT DO NOTHING) реагирует только на уникальные нарушения. Руководство:

ON CONFLICT можно использовать для указания действия, альтернативного поднятию уникального ограничения или ошибке нарушения ограничения исключения. (См. Предложение ON CONFLICT ниже.)

Поскольку выражение VALUES теперь не связано напрямую с INSERT, типы столбцов не являются производными от целевой таблицы. При работе с неосновными типами может потребоваться явное приведение входных значений. Видеть:

person Erwin Brandstetter    schedule 16.03.2016
comment
Это действительно работает очень хорошо, очень ценится. Спасибо за разъяснения по пункту ON CONFLICT. - person peterwimsey; 16.03.2016
comment
Эта работа при вставке в ту же таблицу, на которую я пытаюсь ссылаться? так что-то вроде INSERT INTO entries ... JOIN entries ? - person Juan Carlos Oropeza; 07.02.2017
comment
@JuanCarlosOropeza: вы можете присоединиться к одной и той же таблице таким же образом, просто убедитесь, что условие соединения может соответствовать только одной строке. В противном случае вам придется использовать другую технику. - person Erwin Brandstetter; 07.02.2017
comment
Как насчет таблиц, которые содержат разные типы, такие как метки времени и массивы байтов. Есть ли удобный способ использовать их в FROM ( VALUES (1, 2, 'references two') -- more? ) val (entry_id, referenced_id, name) вместо того, чтобы приводить каждый из них внутри VALUES ? - person madnan; 14.01.2020
comment
@madnan: Вот несколько способов приведения полей отдельного выражения VALUES: stackoverflow.com/a/42217872/939860 - person Erwin Brandstetter; 14.01.2020