INSERT, UPDATE, DELETE и RETURNING

Четыре урока подряд мы только читали данные магазина: выбирали, соединяли, считали итоги. Но магазин живой: приходят новые книги, меняются цены, заказы отменяют. В этом уроке учимся менять данные тремя командами и узнаём, как сразу увидеть результат изменения, не делая второй запрос. Примеры работают на учебной базе магазина.

Тетрадь учёта

У продавца маленького магазина есть тетрадь, куда он записывает книги. С тетрадью он делает ровно три вещи: дописывает новую строку, исправляет старую, вычёркивает лишнюю. И каждый раз, исправив запись, он смотрит на неё ещё раз, чтобы убедиться, что исправил то, что хотел.

В SQL у этих действий свои команды: INSERT дописывает, UPDATE исправляет, DELETE вычёркивает. Вместе с SELECT их называют DML. А «посмотреть ещё раз» в PostgreSQL умеет сама команда: для этого есть RETURNING.

INSERT: дописать строку

В магазин пришёл новый автор и две его книги:

INSERT INTO authors (id, name, country)
VALUES (7, 'Пелевин', 'Россия');

INSERT INTO books (id, title, author_id, genre, price, pages, year) VALUES
  (10, 'Generation П', 7, 'роман', 590.00, 320, 1999),
  (11, 'Чапаев и Пустота', 7, 'роман', 640.00, 400, 1996);

INSERT 0 1
INSERT 0 2

Последнее число в ответе psql это количество вставленных строк. Ноль перед ним остался от старых версий PostgreSQL, на него не смотри.

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

Колонку можно пропустить. Тогда в неё попадёт значение по умолчанию, а если его нет, то NULL. Книга «Омон Ра» вставлена без цены:

INSERT INTO books (id, title, author_id, genre, pages, year)
VALUES (12, 'Омон Ра', 7, 'повесть', 160, 1992);

SELECT id, title, price FROM books WHERE author_id = 7 ORDER BY id;

 id |      title       | price
----+------------------+--------
 10 | Generation П     | 590.00
 11 | Чапаев и Пустота | 640.00
 12 | Омон Ра          |
(3 rows)

Если строка с таким ключом уже есть, ограничение не пустит вставку:

ERROR:  duplicate key value violates unique constraint "books_pkey"
DETAIL:  Key (id)=(1) already exists.

Что делать, когда дубликат ожидаем и надо «вставить или обновить», разберём в следующем уроке.

INSERT … SELECT

Значения для вставки не обязательно писать руками. Их может дать запрос. Магазин устраивает распродажу учебников со скидкой 20%, и цены для неё берутся прямо из books:

CREATE TABLE sale_books (
  book_id integer PRIMARY KEY REFERENCES books (id),
  price   numeric(8, 2) NOT NULL
);

INSERT INTO sale_books (book_id, price)
SELECT id, round(price * 0.8, 2)
FROM books
WHERE genre = 'учебник';

SELECT * FROM sale_books ORDER BY book_id;

 book_id |  price
---------+---------
       7 | 1032.00
       8 | 1992.00
(2 rows)

Вместо VALUES стоит обычный SELECT, и сколько строк он вернёт, столько и вставится.

RETURNING: посмотреть, что получилось

Для отзывов заведём таблицу с автонумерацией из урока про identity:

CREATE TABLE reviews (
  id      integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  book_id integer NOT NULL REFERENCES books (id),
  rating  integer NOT NULL,
  body    text
);

Покупатель оставил отзыв, и приложению нужен его номер, чтобы показать ссылку «ваш отзыв». Номер выдаёт база. Можно сделать второй запрос и поискать последний отзыв, но если в ту же секунду отзыв оставил кто-то ещё, ты получишь чужой номер. RETURNING отдаёт результат прямо из той же команды:

INSERT INTO reviews (book_id, rating, body)
VALUES (3, 5, 'Перечитываю каждый год')
RETURNING id;

 id
----
  1
(1 row)

Для нескольких строк RETURNING вернёт несколько строк, по одной на каждую вставленную:

INSERT INTO reviews (book_id, rating, body) VALUES
  (1, 5, 'Классика'),
  (8, 4, 'Толстая, но полезная')
RETURNING id, book_id;

 id | book_id
----+---------
  2 |       1
  3 |       8
(2 rows)