Самостоятельная ссылочная таблица PostgreSQL - как сохранить родительский идентификатор в скрипте?

У меня есть следующая таблица:

DROP SEQUENCE IF EXISTS CATEGORY_SEQ CASCADE;
CREATE SEQUENCE CATEGORY_SEQ START 1;

DROP TABLE IF EXISTS CATEGORY CASCADE;

CREATE TABLE CATEGORY (
  ID        BIGINT                 NOT NULL DEFAULT nextval('CATEGORY_SEQ'),
  NAME      CHARACTER VARYING(255) NOT NULL,
  PARENT_ID BIGINT
);

ALTER TABLE CATEGORY
  ADD CONSTRAINT CATEGORY_PK PRIMARY KEY (ID);
ALTER TABLE CATEGORY
  ADD CONSTRAINT CATEGORY_SELF_FK FOREIGN KEY (PARENT_ID) REFERENCES CATEGORY (ID);

Теперь мне нужно вставить данные. Итак, я начинаю с родителя:

INSERT INTO CATEGORY (NAME) VALUES ('PARENT_1');

И теперь мне нужен идентификатор только что вставленного родителя, чтобы добавить к нему детей:

INSERT INTO CATEGORY (NAME, PARENT_ID) VALUES ('CHILDREN_1_1', <what_goes_here>);
INSERT INTO CATEGORY (NAME, PARENT_ID) VALUES ('CHILDREN_1_2', <what_goes_here>);

Как я могу получить и сохранить идентификатор родителя, чтобы позже использовать его в последующих вставках?

Источник

3 ответа

Решение

Ответ заключается в использовании RETURNING вместе с WITH

WITH inserted AS (
  INSERT INTO CATEGORY (NAME) VALUES ('PARENT_1')
  RETURNING id
) INSERT INTO CATEGORY (NAME, PARENT_ID) VALUES
  ('CHILD_1_1', (SELECT inserted.id FROM inserted)),
  ('CHILD_2_1', (SELECT inserted.id FROM inserted));
Источник

Вы можете использовать данные, модифицирующие CTE с returning пункт:

with parent_cat (parent_id) as (
   INSERT INTO CATEGORY (NAME) VALUES ('PARENT_1')
   returning id
)
INSERT INTO CATEGORY (NAME, PARENT_ID) 
VALUES 
  ('CHILDREN_1_1', (select parent_id from parent_cat) ), 
  ('CHILDREN_1_2', (select parent_id from parent_cat) );
Источник

(tl;dr: Перейти к варианту 3: Вставить с возвратом)

Напомним, что в postgresql отсутствует концепция "id" для таблиц, а только последовательности (которые обычно, но не обязательно, используются в качестве значений по умолчанию для суррогатных первичных ключей с псевдотипом SERIAL).

Если вы заинтересованы в получении идентификатора недавно вставленной строки, есть несколько способов:

Опция 1: CURRVAL(<sequence name>);,

Например:

  INSERT INTO persons (lastname,firstname) VALUES ('Smith', 'John');
  SELECT currval('persons_id_seq');

Имя последовательности должно быть известно, оно действительно произвольно; в этом примере мы предполагаем, что таблица persons имеет id столбец создан с SERIAL псевдо-типа. Чтобы не полагаться на это и чувствовать себя более чистым, вы можете использовать вместо pg_get_serial_sequence:

  INSERT INTO persons (lastname,firstname) VALUES ('Smith', 'John');
  SELECT currval(pg_get_serial_sequence('persons','id'));

Предостережение: currval() работает только после INSERT (который выполнил nextval()), в том же сеансе.

Вариант 2: LASTVAL();

Это похоже на предыдущее, только вам не нужно указывать порядковый номер: он ищет самую последнюю измененную последовательность (всегда внутри вашего сеанса, то же предупреждение, что и выше).

И то и другое CURRVAL а также LASTVAL полностью безопасны одновременно. Поведение последовательности в PG спроектировано таким образом, что другой сеанс не будет мешать, поэтому нет риска возникновения условий гонки (если другой сеанс вставит еще одну строку между моим INSERT и моим SELECT, я все равно получу правильное значение).

Однако у них есть тонкая потенциальная проблема. Если в базе данных есть какой-то TRIGGER (или RULE), то при вставке в persons таблица, делает некоторые дополнительные вставки в другие таблицы... затем LASTVAL вероятно, даст нам неправильное значение. Проблема может даже случиться с CURRVAL, если дополнительные вставки сделаны в том же самом persons таблица (это гораздо реже, но риск все еще существует).

Вариант 3: INSERT с RETURNING

INSERT INTO persons (lastname,firstname) VALUES ('Smith', 'John') RETURNING id;

Это самый чистый, эффективный и безопасный способ получить удостоверение личности. Это не имеет никакого риска предыдущего.

Недостатки? Почти ничего: вам может понадобиться изменить способ вызова оператора INSERT (в худшем случае, возможно, ваш уровень API или DB не ожидает, что INSERT вернет значение); это не стандартный SQL (кого это волнует); он доступен с Postgresql 8.2 (декабрь 2006...)

Вывод: если можете, перейдите к варианту 3. В другом месте предпочтите 1.

Примечание: все эти методы бесполезны, если вы собираетесь получить последний глобально вставленный идентификатор (не обязательно в вашем сеансе). Для этого вы должны прибегнуть к select max(id) from table (конечно, это не будет читать незафиксированные вставки из других транзакций).

Источник
Другие вопросы по тегам