PostgreSQL: INSERT ON CONFLICT DO UPDATE – Полное руководство

Эквивалентом MySQL INSERT ... ON DUPLICATE KEY UPDATE в PostgreSQL является синтаксис INSERT ... ON CONFLICT DO UPDATE, также известный как операция upsert.

• Обзор базового синтаксиса
• Обработка нескольких строк
• Использование таблицы EXCLUDED
• Практические примеры
• Документация и ресурсы

Обзор базового синтаксиса

INSERT ... ON CONFLICT DO UPDATE в PostgreSQL обеспечивает ту же функциональность, что и ON DUPLICATE KEY UPDATE в MySQL, но с другим синтаксисом. Базовая структура выглядит так:

INSERT INTO table_name (column1, column2, column3)
VALUES (value1, value2, value3)
ON CONFLICT (conflict_target) DO UPDATE SET
    column1 = EXCLUDED.column1,
    column2 = EXCLUDED.column2;

Согласно официальной документации PostgreSQL, предложение ON CONFLICT определяет, что делать, если вставка вызовет нарушение уникального ограничения или ограничения исключения.

Цель конфликта может быть:

• Имя столбца или запятой разделённый список имён столбцов
• Имя ограничения с помощью ON CONFLICT ON CONSTRAINT constraint_name
• ON CONFLICT DO NOTHING, чтобы просто игнорировать конфликт без обновления

Обработка нескольких строк

INSERT ... ON CONFLICT DO UPDATE эффективно обрабатывает несколько строк в одном операторе, что делает его идеальным для массовых операций. Синтаксис остаётся тем же, независимо от того, вставляете ли вы одну строку или несколько:

INSERT INTO table (id, field, field2) 
VALUES 
    (1, 'A', 'X'), 
    (2, 'B', 'Y'), 
    (3, 'C', 'Z')
ON CONFLICT (id) DO UPDATE SET
    field = EXCLUDED.field,
    field2 = EXCLUDED.field2;

Как показано в Data Guide Prisma, такой подход позволяет обрабатывать несколько записей атомарно, повышая производительность за счёт снижения количества обращений к базе данных. Каждая строка, конфликтующая с существующими данными, будет обновлена значениями из EXCLUDED, в то время как неконфликтующие строки будут вставлены как обычно.

Использование таблицы EXCLUDED

Специальная таблица EXCLUDED критична для функциональности upsert в PostgreSQL. Она содержит значения, которые были бы вставлены, если бы конфликт не произошёл. Это позволяет ссылаться на новые значения при обновлении существующих записей.

INSERT INTO inventory (id, name, price, quantity)
VALUES 
    (4, 'Product D', 29.99, 20),
    (5, 'Product E', 39.99, 15)
ON CONFLICT (id) DO UPDATE SET
    name = EXCLUDED.name,
    price = EXCLUDED.price,
    quantity = EXCLUDED.quantity;

Как показано в уроке PostgreSQL от Neon, можно использовать EXCLUDED.* для ссылки на все значения исключения или конкретные столбцы по мере необходимости. Это обеспечивает чистый способ обновления существующих записей новыми данными.

Практические примеры

Ниже приведены практические примеры, демонстрирующие различные сценарии:

Пример 1: Базовый UPSERT с несколькими столбцами

INSERT INTO users (id, username, email, last_login)
VALUES 
    (1, 'john_doe', 'john@example.com', NOW()),
    (2, 'jane_smith', 'jane@example.com', NOW())
ON CONFLICT (id) DO UPDATE SET
    username = EXCLUDED.username,
    email = EXCLUDED.email,
    last_login = EXCLUDED.last_login;

Пример 2: Условные обновления

INSERT INTO products (id, name, price, stock)
VALUES 
    (101, 'Laptop', 999.99, 50),
    (102, 'Mouse', 29.99, 200)
ON CONFLICT (id) DO UPDATE SET
    name = EXCLUDED.name,
    price = EXCLUDED.price,
    stock = products.stock + EXCLUDED.stock;

В этом примере показано, как можно выполнять арифметические операции во время обновления, как предложено в snippets PostgreSQL от W3Resource.

Пример 3: Использование именованных ограничений

INSERT INTO orders (order_id, customer_id, total_amount)
VALUES 
    (5001, 1001, 250.00),
    (5002, 1002, 175.50)
ON CONFLICT ON CONSTRAINT orders_pkey DO UPDATE SET
    customer_id = EXCLUDED.customer_id,
    total_amount = EXCLUDED.total_amount;

Документация и ресурсы

Чтобы узнать больше о функциональности INSERT ... ON CONFLICT DO UPDATE в PostgreSQL, обратитесь к следующим авторитетным ресурсам:

1. Официальная документация PostgreSQL – INSERT – всестороннее покрытие оператора INSERT с предложением ON CONFLICT
2. Урок UPSERT от Neon – практические примеры и объяснения операций upsert
3. Data Guide Prisma – INSERT ON CONFLICT – руководство для разработчиков с реальными кейсами
4. Snippets PostgreSQL Upsert от W3Resource – примеры кода и справочник синтаксиса
5. Stack Overflow – PostgreSQL ON CONFLICT UPDATE – обсуждения сообщества и решения проблем

Заключение

INSERT ... ON CONFLICT DO UPDATE в PostgreSQL предоставляет надёжную и эффективную альтернативу MySQL ON DUPLICATE KEY UPDATE. Ключевые выводы:

• Используйте ON CONFLICT (column_name), чтобы указать, какое уникальное ограничение следует отслеживать
• Ссылайтесь на новые значения через специальную таблицу EXCLUDED в вашем операторе UPDATE
• Синтаксис поддерживает как однострочные, так и многосрочные операции в одном операторе
• Эта функция доступна в PostgreSQL начиная с версии 9.5
• Всегда тестируйте операции upsert на примерах данных, чтобы убедиться, что разрешение конфликтов работает как ожидается

Для сложных сценариев рассмотрите дополнительные варианты, такие как ON CONFLICT DO NOTHING или использование именованных ограничений для более точного контроля над разрешением конфликтов.