INSERT INTO ... SELECT и UPSERT

INSERT INTO ... SELECT и UPSERT

В предыдущих уроках модуля вы работали с одиночными операциями: вставить строку, обновить строку, удалить строку. Этот урок — про два мощных расширения: INSERT INTO ... SELECT для массового копирования данных и ON CONFLICT (UPSERT) для элегантной обработки ситуации «строка уже существует».

INSERT INTO ... SELECT

Вместо VALUES можно использовать SELECT — данные для вставки берутся из другой таблицы или запроса:

INSERT INTO таблица_получатель (col1, col2)
SELECT col1, col2
FROM   таблица_источник
WHERE  условие;

Пример — скопировать московских клиентов в архивную таблицу:

INSERT INTO customers_archive (name, email, city, archived_at)
SELECT name, email, city, NOW()
FROM   customers
WHERE  city = 'Москва';

Особенности:

• Колонки в SELECT должны соответствовать (по типу и порядку) колонкам в INSERT INTO
• SELECT может содержать WHERE, JOIN, GROUP BY — любую сложность
• Это одна транзакция: либо все строки вставились, либо ни одна

Практические сценарии INSERT ... SELECT

Наполнение сводной таблицы:

-- Создать ежемесячный отчёт по продажам
INSERT INTO monthly_sales (year, month, category, revenue, orders_count)
SELECT EXTRACT(YEAR  FROM o.created_at) AS year,
       EXTRACT(MONTH FROM o.created_at) AS month,
       p.category,
       SUM(oi.quantity * oi.unit_price) AS revenue,
       COUNT(DISTINCT o.id)             AS orders_count
FROM   orders      o
JOIN   order_items oi ON oi.order_id   = o.id
JOIN   products    p  ON oi.product_id = p.id
WHERE  o.status = 'completed'
  AND  EXTRACT(MONTH FROM o.created_at) = EXTRACT(MONTH FROM NOW() - INTERVAL '1 month')
GROUP BY 1, 2, 3;

Копирование данных при миграции:

-- Перенести данные из старой схемы в новую
INSERT INTO new_users (id, username, email, created_at)
SELECT user_id, login, email_address, registration_date
FROM   legacy_users
WHERE  active = 1;

Проблема: дубликаты при INSERT

Классическая ситуация: пытаетесь вставить строку, но она уже существует.

INSERT INTO users (username, email)
VALUES ('anna', 'anna@example.com');
-- ERROR: duplicate key value violates unique constraint "users_username_key"

До ON CONFLICT единственный вариант — проверить существование заранее, что неудобно и ненадёжно при конкурентном доступе.

ON CONFLICT: UPSERT

PostgreSQL 9.5+ поддерживает ON CONFLICT — элегантное решение. «UPSERT» = «UPDATE или INSERT»: если строка существует — обновить, если нет — вставить.

Два варианта: проигнорировать конфликт или обновить строку.

DO NOTHING — пропустить при конфликте:

INSERT INTO users (username, email)
VALUES ('anna', 'anna@example.com')
ON CONFLICT (username) DO NOTHING;
-- Если anna существует — запрос завершается без ошибки и без изменений

(username) — колонка, по которой определяется конфликт (должна иметь ограничение UNIQUE или PRIMARY KEY).

DO UPDATE — обновить при конфликте:

INSERT INTO users (username, email, last_seen)
VALUES ('anna', 'anna_new@example.com', NOW())
ON CONFLICT (username) DO UPDATE
SET    email     = EXCLUDED.email,
       last_seen = EXCLUDED.last_seen;

EXCLUDED — специальная «таблица» с новыми значениями из INSERT. EXCLUDED.email — значение, которое пытались вставить. Это позволяет обновить конкретные поля при конфликте.

Проверь себя: что произойдёт с username в строке после DO UPDATE? Изменится или нет?

EXCLUDED: как это работает

EXCLUDED — псевдотаблица, содержащая строку, которую пытались вставить. В DO UPDATE SET можно использовать любые её поля:

INSERT INTO product_prices (product_id, price, updated_at)
VALUES (42, 1500.00, NOW())
ON CONFLICT (product_id) DO UPDATE
SET    price      = EXCLUDED.price,
       updated_at = EXCLUDED.updated_at
WHERE  product_prices.price <> EXCLUDED.price;  -- обновить только если цена изменилась

WHERE в DO UPDATE — дополнительное условие для обновления. Если условие не выполняется — конфликт есть, но строка не обновляется (и ошибки нет).

Практический пример: синхронизация данных

Задача: синхронизировать таблицу товаров из внешнего источника — вставить новые, обновить существующие:

INSERT INTO products (external_id, title, price, category, synced_at)
SELECT external_id, title, price, category, NOW()
FROM   products_import
ON CONFLICT (external_id) DO UPDATE
SET    title     = EXCLUDED.title,
       price     = EXCLUDED.price,
       category  = EXCLUDED.category,
       synced_at = EXCLUDED.synced_at;

Один запрос вместо кода «если EXISTS → UPDATE, ELSE → INSERT». Атомарно, без race conditions, эффективно.

INSERT ... SELECT с ON CONFLICT

Оба можно объединить:

INSERT INTO monthly_summary (user_id, month, total_orders)
SELECT user_id,
       DATE_TRUNC('month', created_at),
       COUNT(*)
FROM   orders
WHERE  status = 'completed'
GROUP BY 1, 2
ON CONFLICT (user_id, month) DO UPDATE
SET total_orders = EXCLUDED.total_orders;

SELECT вычисляет агрегаты, ON CONFLICT обновляет уже существующие строки в сводной таблице. Это идиоматический PostgreSQL-паттерн для инкрементального обновления сводных данных.

Типичные ошибки с ON CONFLICT

1. Колонка без ограничения:

INSERT INTO users (username, email)
VALUES ('anna', 'anna@example.com')
ON CONFLICT (email) DO NOTHING;
-- ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification

ON CONFLICT требует, чтобы указанная колонка имела UNIQUE или PRIMARY KEY. Без ограничения PostgreSQL не знает, как определить «конфликт».

2. Обновление PK-колонки:

ON CONFLICT (id) DO UPDATE SET id = EXCLUDED.id;
-- ERROR: cannot UPDATE id — нельзя обновлять primary key через UPSERT

Обычно первичный ключ не обновляют. В DO UPDATE указывайте только данные, не ключ.

3. Попытка обновить через DO UPDATE колонку, участвующую в ON CONFLICT:

Технически не ошибка в PostgreSQL, но логически бессмысленно — конфликт уже есть по этой колонке, менять её нет смысла. Обычно в DO UPDATE обновляют только не-ключевые поля.

UPSERT в других СУБД

ON CONFLICT — PostgreSQL-синтаксис. В других СУБД:

• MySQL: INSERT ... ON DUPLICATE KEY UPDATE
• SQL Server: MERGE INTO
• SQLite: INSERT OR REPLACE или INSERT ... ON CONFLICT

Концепция одна, синтаксис разный. При переносе кода между СУБД нужна адаптация.

Когда использовать INSERT...SELECT вместо ETL-инструментов

INSERT INTO ... SELECT — это «SQL-ETL» (Extract, Transform, Load). Для несложных трансформаций это идеальный инструмент: минимум кода, работает внутри БД (нет передачи данных по сети), транзакционен.

Примеры, где это уместно:

• Денормализация: скопировать имена в таблицу заказов для отчётов
• Архивирование: перенести старые данные в архивные таблицы
• Агрегация: создать сводные таблицы из детальных данных
• Начальный seed: наполнить таблицы тестовыми данными из других источников

Внешние ETL-инструменты (Apache Airflow, dbt, Fivetran) нужны, когда источник данных — не SQL-таблица, или трансформации слишком сложны для SQL. Для внутрибазовых операций INSERT INTO ... SELECT быстрее, проще и надёжнее.

Создание таблицы из SELECT: CREATE TABLE AS

Родственная операция — создать новую таблицу прямо из результата запроса:

CREATE TABLE top_customers AS
SELECT c.id, c.name, SUM(o.amount) AS total_spent
FROM   customers c
JOIN   orders    o ON o.customer_id = c.id
GROUP BY c.id, c.name
ORDER BY total_spent DESC
LIMIT  100;

Это создаст новую таблицу top_customers с нужной структурой и сразу наполнит её данными. Удобно для анализа, когда нужна «снимок» данных. Подробнее о CREATE TABLE — в следующем модуле.

Краткий итог

• INSERT INTO ... SELECT вставляет строки из результата запроса, любой сложности
• ON CONFLICT (col) DO NOTHING — игнорировать конфликт вставки
• ON CONFLICT (col) DO UPDATE SET ... — обновить при конфликте (UPSERT)
• EXCLUDED — псевдотаблица с новыми значениями из INSERT-части
• DO UPDATE WHERE — опциональное дополнительное условие для обновления

DML: итоговая картина модуля

За четыре урока вы освоили все операторы изменения данных:

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

Что дальше

Вы завершили модуль 8 — DML: INSERT, UPDATE, DELETE, UPSERT. Следующий модуль — DDL: создание таблиц, типы данных, ограничения. Вы научитесь не только работать с готовыми таблицами, но и создавать собственные.