INSERT, UPDATE и DELETE в SQL: примеры на PostgreSQL

INSERT добавляет строки в таблицу, UPDATE меняет значения в существующих строках, а DELETE удаляет строки. У UPDATE и DELETE есть общая ловушка: без WHERE они задевают все строки таблицы. Ниже — товары и заказы магазина на PostgreSQL 17 с живым выводом psql: RETURNING, обновление из другой таблицы, удаление по связи, TRUNCATE и разбор ошибок. Все примеры можно повторить в песочнице SQL.

SQL / 01

Без WHERE команда задевает все строки

Число в ответе базы — сколько строк изменилось. Если оно больше, чем ты ждал, а транзакция ещё открыта, ROLLBACK вернёт данные как были.

Таблицы для примеров

Товары лежат в products, заказы в orders ссылаются на товары внешним ключом, в price_updates — новые цены, а products_archive — архив дорогих товаров. Номер id выдаёт база, in_stock по умолчанию true. Что значит каждая строка, разобрано в материале о CREATE TABLE:

CREATE TABLE products (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name text NOT NULL,
    price numeric(10, 2) NOT NULL,
    in_stock boolean NOT NULL DEFAULT true
);

CREATE TABLE orders (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    product_id bigint NOT NULL REFERENCES products (id),
    quantity integer NOT NULL
);

CREATE TABLE price_updates (
    product_id bigint PRIMARY KEY,
    new_price numeric(10, 2) NOT NULL
);

CREATE TABLE products_archive (
    name text NOT NULL,
    price numeric(10, 2) NOT NULL
);

INSERT: добавить строки

После имени таблицы в скобках перечисляют столбцы, а в VALUES — значения в том же порядке. Столбцы, которых нет в списке, получают значение по умолчанию: здесь это id и in_stock.

INSERT INTO products (name, price) VALUES ('Клавиатура', 3490.00);

INSERT 0 1

Второе число в ответе — сколько строк вставлено. Первое осталось от старых версий PostgreSQL, в нём всегда 0. Несколько строк вставляют одной командой: наборы значений перечисляют через запятую. RETURNING возвращает то, что получилось, в том числе номера, которые выдала база:

INSERT INTO products (name, price, in_stock) VALUES
    ('Мышь', 1290.00, true),
    ('Монитор', 18990.00, true),
    ('Коврик', 490.00, false),
    ('Веб-камера', 4590.00, true)
RETURNING id, name, price;

 id |    name    |  price   
----+------------+----------
  2 | Мышь       |  1290.00
  3 | Монитор    | 18990.00
  4 | Коврик     |   490.00
  5 | Веб-камера |  4590.00
(4 rows)

INSERT 0 4

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

INSERT INTO products_archive (name, price)
SELECT name, price FROM products WHERE price > 10000;

INSERT 0 1

В архив попал монитор. Тем же INSERT заполним новые цены и заказы, они понадобятся в разделах про UPDATE и DELETE:

INSERT INTO price_updates (product_id, new_price) VALUES (1, 3290.00), (3, 17990.00);
INSERT INTO orders (product_id, quantity) VALUES (1, 2), (2, 1), (2, 3);

INSERT 0 2
INSERT 0 3

Если строка уже может существовать и её нужно не вставить, а обновить, используют INSERT ... ON CONFLICT. Он разобран в материале об upsert в PostgreSQL.

UPDATE: изменить строки

Перед изменениями в products пять товаров:

SELECT id, name, price, in_stock FROM products ORDER BY id;

 id |    name    |  price   | in_stock 
----+------------+----------+----------
  1 | Клавиатура |  3490.00 | t
  2 | Мышь       |  1290.00 | t
  3 | Монитор    | 18990.00 | t
  4 | Коврик     |   490.00 | f
  5 | Веб-камера |  4590.00 | t
(5 rows)

SET задаёт новые значения, WHERE — какие строки менять:

UPDATE products SET price = 1390.00 WHERE id = 2;

UPDATE 1

Значение можно вычислить из старого, а столбцов поменять сразу несколько — через запятую. RETURNING покажет результат:

UPDATE products SET price = price * 1.10, in_stock = true
WHERE id = 4
RETURNING id, name, price, in_stock;

 id |  name  | price  | in_stock 
----+--------+--------+----------
  4 | Коврик | 539.00 | t
(1 row)

UPDATE 1

Новые значения часто лежат в другой таблице, как цены в price_updates. Для этого в PostgreSQL есть UPDATE ... FROM: вторую таблицу подключают в FROM, а связь задают в WHERE:

UPDATE products p SET price = u.new_price
FROM price_updates u
WHERE u.product_id = p.id
RETURNING p.id, p.name, p.price;

 id |    name    |  price   
----+------------+----------
  1 | Клавиатура |  3290.00
  3 | Монитор    | 17990.00
(2 rows)

UPDATE 2

Изменились только товары, для которых нашлась новая цена: клавиатура и монитор.

DELETE: удалить строки

DELETE удаляет строки, подходящие под WHERE, а RETURNING показывает, что именно удалено:

DELETE FROM products WHERE id = 5 RETURNING id, name;

 id |    name    
----+------------
  5 | Веб-камера
(1 row)

DELETE 1

Строку, на которую ссылаются другие таблицы, внешний ключ удалить не даст. На мышь есть заказы:

DELETE FROM products WHERE id = 2;

ERROR:  update or delete on table "products" violates foreign key constraint "orders_product_id_fkey" on table "orders"
DETAIL:  Key (id)=(2) is still referenced from table "orders".

Чтобы удалить строки по условию из другой таблицы, есть DELETE ... USING — аналог UPDATE ... FROM. Здесь удаляются заказы, в которых товар называется «Мышь»:

DELETE FROM orders o
USING products p
WHERE o.product_id = p.id AND p.name = 'Мышь'
RETURNING o.id, o.quantity;

 id | quantity 
----+----------
  2 |        1
  3 |        3
(2 rows)

DELETE 2

Теперь мышь удаляется без ошибки:

DELETE FROM products WHERE id = 2;

DELETE 1

Что делать со связанными строками при удалении — запрещать или удалять вместе с товаром, — задаёт ON DELETE во внешнем ключе.

Без WHERE изменятся все строки

UPDATE и DELETE без WHERE применяются ко всей таблице, и PostgreSQL ни о чём не спросит. Вот как это выглядит внутри транзакции и как её откатить:

BEGIN;
UPDATE products SET price = 0;
SELECT id, name, price FROM products ORDER BY id;
ROLLBACK;
SELECT id, name, price FROM products ORDER BY id;

BEGIN
UPDATE 3
 id |    name    | price 
----+------------+-------
  1 | Клавиатура |  0.00
  3 | Монитор    |  0.00
  4 | Коврик     |  0.00
(3 rows)

ROLLBACK
 id |    name    |  price   
----+------------+----------
  1 | Клавиатура |  3290.00
  3 | Монитор    | 17990.00
  4 | Коврик     |   539.00
(3 rows)

ROLLBACK вернул цены, потому что изменения не подтвердили через COMMIT. Без BEGIN каждая команда в psql подтверждается сразу, и откатывать будет нечего. Поэтому перед массовым изменением сначала запусти SELECT с тем же WHERE и посмотри, сколько строк он нашёл:

SELECT id, name, price FROM products WHERE price < 1000;
UPDATE products SET price = 590.00 WHERE price < 1000;

 id |  name  | price  
----+--------+--------
  4 | Коврик | 539.00
(1 row)

UPDATE 1

Совпало: SELECT нашёл одну строку, UPDATE изменил одну. Как транзакции изолируют изменения от других подключений, разобрано в материале о транзакциях и уровнях изоляции.

Главная мысль: UPDATE и DELETE начинают с WHERE. Сначала проверь условие обычным SELECT, а рискованное изменение делай внутри BEGIN — тогда ошибку исправит ROLLBACK.

TRUNCATE: очистить таблицу целиком

TRUNCATE удаляет все строки сразу. По документации PostgreSQL он делает то же, что DELETE без условия, но быстрее, потому что не читает таблицу, и сразу освобождает место на диске. Таблицу, на которую ссылается внешний ключ, он в одиночку не очистит:

TRUNCATE products;

ERROR:  cannot truncate a table referenced in a foreign key constraint
DETAIL:  Table "orders" references "products".
HINT:  Truncate table "orders" at the same time, or use TRUNCATE ... CASCADE.

Таблицу заказов, на которую никто не ссылается, очистить можно:

TRUNCATE orders;

TRUNCATE TABLE

За скорость платят блокировкой: TRUNCATE берёт ACCESS EXCLUSIVE, и до конца его транзакции остальные запросы к таблице ждут, даже обычный SELECT. Как найти запрос, который держит блокировку, разобрано в материале о блокировках PostgreSQL. Ещё два отличия от DELETE: TRUNCATE не запускает триггеры ON DELETE, а с RESTART IDENTITY заодно сбрасывает счётчики номеров. Откатывается он так же, как DELETE, если транзакцию не подтвердили.

Ошибки, на которых спотыкаются

INSERT has more expressions than target columns. Значений больше, чем столбцов в списке:

ERROR:  INSERT has more expressions than target columns
LINE 1: ...O products (name, price) VALUES ('Наушники', 2990.00, true);
                                                                 ^

invalid input syntax for type. Значение не подходит к типу столбца — например, слово в столбец с ценой:

ERROR:  invalid input syntax for type numeric: "дорого"
LINE 1: ...T INTO products (name, price) VALUES ('Наушники', 'дорого');
                                                             ^

INSERT без списка столбцов. Тогда значения идут по порядку столбцов таблицы, начиная с id, и название пытается лечь в номер:

ERROR:  invalid input syntax for type bigint: "Наушники"
LINE 1: INSERT INTO products VALUES ('Наушники', 2990.00);
                                     ^

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

more than one row returned by a subquery. Подзапрос в SET вернул несколько строк, и PostgreSQL не знает, какую взять:

ERROR:  more than one row returned by a subquery used as an expression

Свяжи подзапрос с обновляемой строкой или перепиши запрос через UPDATE ... FROM.

violates foreign key constraint при DELETE. На строку ссылаются другие таблицы. Удали сначала зависимые строки или настрой ON DELETE во внешнем ключе.

cannot truncate a table referenced in a foreign key constraint. Очищай связанные таблицы одной командой: TRUNCATE orders, products;. Вариант TRUNCATE products CASCADE очистит заодно все таблицы, которые ссылаются на products, и сообщит об этом строкой NOTICE: truncate cascades to table "orders".

syntax error at or near “JOIN”. Это UPDATE ... JOIN из MySQL, в PostgreSQL его нет:

ERROR:  syntax error at or near "JOIN"
LINE 1: UPDATE products p JOIN price_updates u ON u.product_id = p.i...
                          ^

Вместо него — UPDATE ... FROM, как в примере выше.

Частые вопросы

Как вставить несколько строк одной командой

Перечисли наборы значений через запятую после VALUES, как в примере с мышью, монитором и ковриком. Это быстрее, чем отдельная команда на каждую строку.

Как обновить таблицу данными из другой таблицы

Через UPDATE ... FROM: вторую таблицу перечисляют в FROM, а связь строк задают в WHERE. Обновятся только строки, для которых нашлась пара. Запись UPDATE ... JOIN из MySQL в PostgreSQL даёт синтаксическую ошибку.

Чем DELETE отличается от TRUNCATE

DELETE удаляет строки по условию и запускает триггеры. TRUNCATE очищает всю таблицу без условия, работает быстрее и сразу освобождает место, но на время транзакции блокирует таблицу целиком. Для удаления части строк подходит только DELETE.

Как узнать, сколько строк изменилось

Число в ответе базы: UPDATE 1, DELETE 2, INSERT 0 4. А RETURNING возвращает сами строки — с новыми значениями после UPDATE и с удалёнными после DELETE.

Где потренироваться

Повтори эти команды в песочнице SQL: создай таблицы, вставь строки, попробуй UPDATE без WHERE внутри BEGIN и откати его. Запросы к готовым таблицам — соединения, группировки, оконные функции — отрабатывают задачи тренажёра SQL.