Одновременная вставка данных в PostgreSQL: метод RETURNING ID

Быстрый ответ

Для извлечения id в PostgreSQL следует использовать выражение INSERT INTO...SELECT, дополненное RETURNING внутри CTE (Common Table Expressions):

WITH inserted AS (
  INSERT INTO target_table (col1, col2)
  SELECT col1, col2 FROM source_table
  RETURNING id
)
SELECT id FROM inserted;

Исполняя этот запрос, вы вставляете данные из source_table в target_table и сразу получаете новые id для дальнейшего использования.

Применение RETURNING в транзакциях

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

BEGIN;

WITH inserted AS (
  INSERT INTO clients (name, email)
  SELECT name, email FROM new_clients_temp
  RETURNING id
)
SELECT id INTO my_new_client_id FROM inserted;

UPDATE orders SET client_id = my_new_client_id WHERE order_id = my_target_order_id;

COMMIT;

Применение RETURNING в транзакции и сохранение результатов в переменной обеспечивают эффективность и плавность выполнения операций в рамках одной транзакции.

Отложенная вставка пакетов с использованием массивов

Для вставки большого количества строк и получения их id используйте массивы или сразу обрабатывайте id:

WITH inserted AS (
  INSERT INTO blogs (title, content)
  SELECT title, content FROM imported_blogs
  RETURNING id
)
SELECT array_agg(id) FROM inserted;

этот подход позволяет выполнять массовые операции и удобно собирать все новые идентификаторы.

Визуализация

Процесс INSERT FROM SELECT RETURNING ID в PostgreSQL визуализируется так:

⚙️ Транспортировка данных: КОПИРОВАНИЕ ➡️ СОЗДАНИЕ + ИДЕНТИФИКАЦИЯ 🏷️

В SQL это выглядит следующим образом:

INSERT INTO destination_table
SELECT * FROM source_table
RETURNING id;

Снова визуализируем наш процесс:

Задача: 📜[Исходные данные]
Результат: 📊[Новые данные] ➕ 🏷️[ID]

Данный процесс упрощает понимание переноса данных из одной таблицы в другую с идентификацией по уникальному id.

Автоматизация с помощью функций и триггеров

Для автоматизации обработки данных можно сочетать RETURNING id с функциями и триггерами базы данных:

CREATE OR REPLACE FUNCTION insert_with_trigger() RETURNS TRIGGER AS $$
BEGIN
  NEW.dealer_id := (INSERT INTO dealers (name) VALUES (NEW.dealer_name) RETURNING id);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_before_insert
BEFORE INSERT ON vehicles
FOR EACH ROW EXECUTE FUNCTION insert_with_trigger();

Такой подход упрощает логику создания связанных записей, например, между транспортными средствами и дилерами.

Советы, ограничения и возможные проблемы

• Осторожно, NULL: Убедитесь, что ваш SELECT не содержит неожиданных значений NULL.
• Типы данных: При выполнении операций вставки следите за совместимостью типов данных.
• Исключите дублирование: Используйте DISTINCT в SELECT обдуманно, чтобы предотвратить повторную вставку.
• Оптимальное выполнение: Для работы с большими объёмами данных используйте батчинг или другие методы оптимизации.

Полезные материалы

1. PostgreSQL INSERT RETURNING Clause — Учебная статья про INSERT RETURNING.
2. The SQL INSERT INTO SELECT Statement – W3Schools — Основы SQL INSERT INTO SELECT.
3. PostgreSQL Documentation: INSERT — Официальное руководство PostgreSQL по INSERT.
4. Stack Overflow: INSERT INTO...RETURNING example — Примеры использования RETURNING.
5. PostgresOpen 2019 — Материалы конференции о текущем состоянии проекта PostgreSQL.