Как вставить строку, содержащую внешний ключ?

Использование PostgreSQL v9.1. У меня есть следующие таблицы:

CREATE TABLE foo
(
    id BIGSERIAL     NOT NULL UNIQUE PRIMARY KEY,
    type VARCHAR(60) NOT NULL UNIQUE
);

CREATE TABLE bar
(
    id BIGSERIAL NOT NULL UNIQUE PRIMARY KEY,
    description VARCHAR(40) NOT NULL UNIQUE,
    foo_id BIGINT NOT NULL REFERENCES foo ON DELETE RESTRICT
);

Скажите, что первая таблица foo заполняется следующим образом:

INSERT INTO foo (type) VALUES
    ( 'red' ),
    ( 'green' ),
    ( 'blue' );

Можно ли легко вставить строки в bar, указав таблицу foo? Или я должен сделать это в два этапа, сначала посмотрев тип foo, который я хочу, а затем вставив новую строку в строку bar?

Вот пример псевдокода, показывающий, что я надеялся сделать:

INSERT INTO bar (description, foo_id) VALUES
    ( 'testing',     SELECT id from foo WHERE type='blue' ),
    ( 'another row', SELECT id from foo WHERE type='red'  );
40 голосов | спросил Stéphane 16 J000000Tuesday13 2013, 08:07:10

3 ответа


52

Ваш синтаксис почти хорош, ему нужны скобки вокруг подзапросов, и он будет работать:

INSERT INTO bar (description, foo_id) VALUES
    ( 'testing',     (SELECT id from foo WHERE type='blue') ),
    ( 'another row', (SELECT id from foo WHERE type='red' ) );

Протестировано в SQL-скрипте

Другой способ, с более коротким синтаксисом, если у вас есть много значений для вставки:

WITH ins (description, type) AS
( VALUES
    ( 'more testing',   'blue') ,
    ( 'yet another row', 'green' )
)  
INSERT INTO bar
   (description, foo_id) 
SELECT 
    ins.description, foo.id
FROM 
  foo JOIN ins
    ON ins.type = foo.type ;
ответил ypercubeᵀᴹ 16 J000000Tuesday13 2013, 10:09:39
30

Обычный INSERT

INSERT INTO bar (description, foo_id)
SELECT val.description, f.id
FROM  (
   VALUES
      (text 'testing', text 'blue')  -- explicit type declaration; see below
    , ('another row', 'red' )
    , ('new row1'   , 'purple')      -- purple does not exist in foo, yet
    , ('new row2'   , 'purple')
   ) val (description, type)
LEFT   JOIN foo f USING (type);
  • Использование LEFT [OUTER] JOIN вместо [INNER] JOIN означает, что строки из val не отбрасываются , если совпадение не найдено в foo. Вместо этого NULL вводится для foo_id.

  • Выражение VALUES в подзапросе делает то же самое, что @ ypercube's CTE. Общие выражения таблицы предлагают дополнительные функции и их легче читать в больших запросов, но они также представляют собой оптимизационные барьеры. Таким образом, подзапросы, как правило, немного быстрее, когда ни одно из указанных выше не требуется.

  • id, поскольку имя столбца является широко распространенным анти-шаблоном. Должен быть foo_id и bar_id или что-либо описательное. При объединении нескольких таблиц вы получаете несколько столбцов с именем id ...

  • Рассмотрим простой текст text или varchar вместо varchar(n). Если вам действительно необходимо наложить ограничение по длине, добавьте ограничение CHECK:

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

INSERT пропускает строки FK в то же время

Если вы хотите создать несуществующие записи в foo на лету, в выражении одиночного SQL , CTE являются инструментальными:

WITH sel AS (
   SELECT val.description, val.type, f.id AS foo_id
   FROM  (
      VALUES
         (text 'testing', text 'blue')
       , ('another row', 'red'   )
       , ('new row1'   , 'purple')
       , ('new row2'   , 'purple')
      ) val (description, type)
   LEFT   JOIN foo f USING (type)
   )
, ins AS ( 
   INSERT INTO foo (type)
   SELECT DISTINCT type FROM sel WHERE foo_id IS NULL
   RETURNING id AS foo_id, type
   )
INSERT INTO bar (description, foo_id)
SELECT sel.description, COALESCE(sel.foo_id, ins.foo_id)
FROM   sel
LEFT   JOIN ins USING (type);

Обратите внимание на две новые фиктивные строки для вставки. Оба являются фиолетовыми , которые еще не существуют в foo. Два , чтобы проиллюстрировать необходимость в DISTINCT в первом выражении INSERT.

Пошаговое объяснение

  1. 1-й CTE sel предоставляет несколько строк входных данных. Подзапрос val с выражением VALUES может быть заменен таблицей или подзапросом в качестве источника. Сразу LEFT JOIN в foo добавьте foo_id для ранее существовавших type строк. Все остальные строки получают foo_id IS NULL таким образом.

  2. Второй CTE ins вставляет отдельные новые типы (foo_id IS NULL) в foo, и возвращает вновь сгенерированный foo_id - вместе с типом type, чтобы присоединиться к вставке строк.

  3. Окончательный внешний INSERT теперь может вставить foo.id для каждой строки: либо существующий тип, либо он был вставлен на шаге 2.

Строго говоря, обе вставки происходят «параллельно», но поскольку это оператор single , ограничения по умолчанию FOREIGN KEY не будут жаловаться. Ссылочная целостность применяется по умолчанию в конце инструкции.

SQL Fiddle для Postgres 9.3. ( То же самое работает в 9.1.)

Если вы выполняете несколько из этих запросов одновременно, существует незначительное условие гонки . Подробнее о связанных вопросах здесь и here и . В самом делепроисходит только при сильной параллельной нагрузке, если вообще когда-либо. По сравнению с решениями кэширования, например, рекламируемыми в другом ответе, вероятность супер-крошечная .

Функция для повторного использования

Для повторного использования я бы создал функцию SQL, которая принимает массив записей в качестве параметра и вместо кода> VALUES используется unnest(param).

Или, если синтаксис для массивов записей слишком запутан для вас, используйте строку с разделителями-запятыми в качестве параметра VALUES. Например, форма:

_param

Затем используйте это, чтобы заменить выражение 'description1,type1;description2,type2;description3,type3' в приведенном выше выражении:

VALUES


Функция с UPSERT в Postgres 9.5

Создайте собственный тип строки для передачи параметров. Мы могли обойтись без него, но это проще:

SELECT split_part(x, ',', 1) AS description
       split_part(x, ',', 2) AS type
FROM unnest(string_to_array(_param, ';')) x;

Функции:

CREATE TYPE foobar AS (description text, type text);

Вызов:

CREATE OR REPLACE FUNCTION f_insert_foobar(VARIADIC _val foobar[])
  RETURNS void AS
$func$
   WITH val AS (SELECT * FROM unnest(_val))    -- well-known row type
   ,    ins AS ( 
      INSERT INTO foo AS f (type)
      SELECT DISTINCT v.type                   -- DISTINCT!
      FROM   val v
      ON     CONFLICT(type) DO UPDATE          -- type already exists
      SET    type = excluded.type WHERE FALSE  -- never executed, but lock rows
      RETURNING f.type, f.id
      )
   INSERT INTO bar AS b (description, foo_id)
   SELECT v.description, COALESCE(f.id, i.id)  -- assuming most types pre-exist
   FROM        val v
   LEFT   JOIN foo f USING (type)              -- already existed
   LEFT   JOIN ins i USING (type)              -- newly inserted
   ON     CONFLICT (description) DO UPDATE     -- description already exists
   SET    foo_id = excluded.foo_id             -- real UPSERT this time
   WHERE  b.foo_id IS DISTINCT FROM excluded.foo_id  -- only if actually changed
$func$  LANGUAGE sql;

Быстрое и надежное решение для сред с параллельными транзакциями.

В дополнение к запросам выше, это ...

  • ... применяется SELECT f_insert_foobar( '(testing,blue)' , '(another row,red)' , '(new row1,purple)' , '(new row2,purple)' , '("with,comma",green)' -- added to demonstrate row syntax ); или SELECT в INSERT: Любой foo, который не существует в FK таблица, однако, вставлена. Предполагая, что большинство типов уже существует. Чтобы быть абсолютно уверенными и исключать условия гонки, существующие строки, которые нам нужны, заблокированы (так что параллельные транзакции не могут помешать). Если это слишком параноидально для вашего случая, вы можете заменить:

    type

    с

      ON     CONFLICT(type) DO UPDATE          -- type already exists
      SET    type = excluded.type WHERE FALSE  -- never executed, but lock rows
    
  • ... применяет ON CONFLICT(type) DO NOTHING или INSERT (true "UPSERT") на панели UPDATE: Если bar уже существует его тип description:

    type

    Но только если ON CONFLICT (description) DO UPDATE -- description already exists SET foo_id = excluded.foo_id -- real UPSERT this time WHERE b.foo_id IS DISTINCT FROM excluded.foo_id -- only if actually changed действительно изменяется:

  • ... передает значения как известные типы строк с параметром type. Обратите внимание на значение по умолчанию не более 100 параметров! Для сравнения:

    Есть много других способов передать несколько строк ...

по теме:

ответил Erwin Brandstetter 17 J000000Wednesday13 2013, 00:40:05
3

Поиск. В основном вам нужно, чтобы id foo вставлял их в панель.

Не устанавливается postgres, кстати. (и вы не отметили его так) - это, как правило, работает SQL. Здесь нет ярлыков.

Применение мудрое, тем не менее, у вас может быть кеш foo-элементов в памяти. Мои таблицы часто имеют до 3 уникальных полей:

  • Идентификатор (целое или что-то еще), который является основным ключом на уровне таблицы.
  • Идентификатор, который является идентификатором GUID, который используется как устойчивый уровень приложения ID-приложения (и может быть выставлен клиенту в URL-адресах и т. д.).
  • Код - строка, которая может быть там, и должна быть уникальной, если она есть (sql server: отфильтрован уникальный индекс, а не null). Это идентификатор набора клиентов.

Пример:

  • Аккаунт (в торговом приложении) - > Id - это int, используемый для внешних ключей. - > Идентификатор является ориентиром и используется в веб-порталах и т. Д. - всегда принимается. - > Код установлен вручную. Правило: после его установки он не изменяется.

Очевидно, что если вы хотите связать что-то с учетной записью - сначала вы должны технически получить идентификатор, но при условии, что оба идентификатора и кода никогда не меняются, как только они есть, положительный кэш в памяти kan останавливает большинство поисков от попадания в базу данных .

ответил TomTom 16 J000000Tuesday13 2013, 09:23:10

Похожие вопросы

Популярные теги

security × 330linux × 316macos × 2827 × 268performance × 244command-line × 241sql-server × 235joomla-3.x × 222java × 189c++ × 186windows × 180cisco × 168bash × 158c# × 142gmail × 139arduino-uno × 139javascript × 134ssh × 133seo × 132mysql × 132