INSERT, UPDATE, DELETE в PostgreSQL — примеры и синтаксис

Три базовые операции изменения данных в PostgreSQL. Разбираем синтаксис с нюансами: множественная вставка, UPSERT через ON CONFLICT, RETURNING для получения результата, UPDATE из другой таблицы.

INSERT — добавление данных

-- Вставить одну строку:
INSERT INTO users (email, username, age)
VALUES ('user@example.com', 'johndoe', 25);

-- Вставить несколько строк за раз (значительно быстрее чем по одной):
INSERT INTO users (email, username, age) VALUES
  ('alice@example.com', 'alice', 30),
  ('bob@example.com', 'bob', 25),
  ('carol@example.com', 'carol', 28);

-- Вставить из другой таблицы:
INSERT INTO users_archive
SELECT * FROM users WHERE created_at < '2024-01-01';

-- Вставить и получить сгенерированный ID:
INSERT INTO users (email, username)
VALUES ('new@example.com', 'newuser')
RETURNING id;

-- Получить всю вставленную строку:
INSERT INTO users (email, username)
VALUES ('new@example.com', 'newuser')
RETURNING *;

-- RETURNING с несколькими полями:
INSERT INTO orders (user_id, amount, status)
VALUES (1, 500.00, 'pending')
RETURNING id, created_at;

ON CONFLICT — UPSERT (вставить или обновить)

UPSERT — вставить строку, а если нарушается UNIQUE ограничение — обновить существующую. В PostgreSQL это реализуется через ON CONFLICT:

-- Если email уже занят — обновить username:
INSERT INTO users (email, username)
VALUES ('existing@example.com', 'newname')
ON CONFLICT (email)
DO UPDATE SET username = EXCLUDED.username;
-- EXCLUDED — псевдотаблица с новыми значениями которые пытались вставить

-- Если уже существует — ничего не делать (просто игнорировать):
INSERT INTO users (email, username)
VALUES ('existing@example.com', 'newname')
ON CONFLICT (email) DO NOTHING;

-- Обновить несколько полей при конфликте:
INSERT INTO users (email, username, score, updated_at)
VALUES ('user@example.com', 'user', 100, NOW())
ON CONFLICT (email) DO UPDATE SET
  username   = EXCLUDED.username,
  score      = users.score + EXCLUDED.score,  -- прибавить к текущему
  updated_at = EXCLUDED.updated_at;

-- ON CONFLICT с именованным constraint:
INSERT INTO products (sku, name, price)
VALUES ('ABC-001', 'Widget', 9.99)
ON CONFLICT ON CONSTRAINT products_sku_unique
DO UPDATE SET price = EXCLUDED.price;

UPDATE — обновление данных

-- Обновить одно поле:
UPDATE users SET active = FALSE WHERE id = 42;

-- Обновить несколько полей:
UPDATE users
SET username   = 'new_name',
    score      = score + 10,    -- арифметика с текущим значением
    updated_at = NOW()
WHERE email = 'user@example.com';

-- ВНИМАНИЕ: UPDATE без WHERE изменит ВСЕ строки:
UPDATE products SET discount = 0;  -- обнулит скидку у всех товаров

-- Обновить и получить изменённые строки:
UPDATE users
SET active = TRUE
WHERE last_login > NOW() - INTERVAL '30 days'
RETURNING id, email, active;

-- UPDATE из другой таблицы (UPDATE ... FROM):
UPDATE orders o
SET status = 'shipped'
FROM shipments s
WHERE s.order_id = o.id
  AND s.shipped_at IS NOT NULL
  AND o.status = 'paid';

-- Обновить на основе подзапроса:
UPDATE products
SET price = price * 0.9
WHERE id IN (
  SELECT product_id
  FROM order_items
  GROUP BY product_id
  HAVING COUNT(*) > 100  -- скидка на самые популярные товары
);

DELETE — удаление данных

-- Удалить конкретную строку:
DELETE FROM users WHERE id = 42;

-- Удалить по условию:
DELETE FROM sessions WHERE expires_at < NOW();

-- ВНИМАНИЕ: DELETE без WHERE удалит ВСЕ строки (сама таблица остаётся):
DELETE FROM temp_data;
-- Быстрее для полной очистки использовать TRUNCATE:
TRUNCATE TABLE temp_data;
TRUNCATE TABLE temp_data RESTART IDENTITY;  -- + сбросить sequence

-- Получить удалённые строки:
DELETE FROM cart_items
WHERE cart_id = 100
RETURNING product_id, quantity;

-- Удалить из таблицы на основе данных другой (DELETE ... USING):
DELETE FROM order_items oi
USING orders o
WHERE oi.order_id = o.id
  AND o.status = 'cancelled';

-- Удалить дубликаты (оставить только строки с минимальным id):
DELETE FROM users a
USING users b
WHERE a.email = b.email
  AND a.id > b.id;

COPY — массовая загрузка данных

COPY — самый быстрый способ загрузить большой объём данных в PostgreSQL, значительно быстрее массового INSERT:

-- COPY (требует суперпользователя, путь на сервере):
COPY users (email, username, age)
FROM '/var/lib/postgresql/users.csv'
WITH (FORMAT csv, HEADER true, DELIMITER ',');

-- \copy (psql клиент, файл на вашей машине — работает без суперпользователя):
\copy users (email, username, age) FROM 'users.csv' CSV HEADER

-- С указанием NULL значений:
\copy users FROM 'users.csv' CSV HEADER NULL ''

-- Экспорт в CSV:
\copy users TO 'users_export.csv' CSV HEADER

-- Экспорт результата запроса:
\copy (SELECT id, email, created_at FROM users WHERE active = TRUE) \
  TO 'active_users.csv' CSV HEADER

Часто задаваемые вопросы

Как получить ID только что вставленной записи?

Добавить RETURNING id к команде INSERT: INSERT INTO users (email) VALUES ('a@b.com') RETURNING id; — вернёт id вставленной строки. Можно вернуть несколько полей или все: RETURNING *. Это атомарная операция — вернёт правильный id даже при параллельных вставках. Не нужно использовать lastval() или SELECT MAX(id) — это небезопасно в конкурентной среде.

Как в PostgreSQL сделать INSERT OR UPDATE?

Через ON CONFLICT: INSERT INTO users (email, name) VALUES ('a@b.com', 'Alice') ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name;. Здесь EXCLUDED — это псевдотаблица с новыми значениями которые пытались вставить. Если нужно игнорировать дублирующиеся записи без ошибки: ON CONFLICT DO NOTHING. Работает только если есть UNIQUE ограничение или индекс на конфликтующем поле.

Как массово загрузить данные в PostgreSQL?

Три варианта по скорости: 1) COPY / \copy — самый быстрый, предназначен для bulk-загрузки, читает CSV напрямую. 2) Множественный INSERT: INSERT INTO t VALUES (...), (...), (...); — вставить тысячи строк одним запросом значительно быстрее чем по одной. 3) Один INSERT за раз в цикле — медленно, не используйте для больших объёмов. Для загрузки миллионов строк: временно отключить индексы, загрузить данные, создать индексы заново.