Полезное в PostgreSQL, часть 2

Прошлый пост собрал столько огоньков, что аж на душе потеплело, спасибо❤️

Продолжим наш ликбез по SQL. Сегодня расскажу про 3 похожие конструкции для вложенных запросов: CTE, View и Materialized View.

⭐️ Сommon Table Expression (СТЕ) выглядит так:

WITH cte_name AS (
  SELECT …
)
SELECT … FROM cte_name;

cte_name используется как источник данных

⭐️ VIEW и MATERIALIZED VIEW выглядят похоже:

CREATE (MATERIALIZED) VIEW view_name AS
   SELECT … ;

Полученное вью также используется как источник данных:

SELECT … FROM view_name;

В чём же разница?

1️⃣ Что именно хранится

VIEW — это просто сокращение запроса, результат выполнения не сохраняется. При каждом FROM view_name запрос выполняется заново.

MATERIALIZED VIEW сохраняет результат на момент выполнения. По сути создаётся временная таблица с копией данных.

Важный момент: если исходные данные поменяются, они не повлияют на данные в MATERIALIZED VIEW. Для актуализации данных надо отдельно вызвать команду REFRESH.

СТЕ как MATERIALIZED VIEW сохраняет результат выполнения и считается один раз.

2️⃣ Видимость

CTE не существует сам по себе, за ним обязательно должен следовать запрос, который его использует. Можно сказать, что область видимости и время жизни CTE — один запрос.

VIEW и MATERIALIZED VIEW доступны на уровне схемы, можно пользоваться много раз из разных мест. Удалять вью надо явно командой DROP.

Это основные отличия. Есть ещё несколько, но не будем углубляться:)

Примерные кейсы использования:

🔧 CTE — сделать сложные запросы более читаемыми
🔨 View — синоним для популярного запроса на выборку
🪛 Materialized view — снимок данных, которые долго считать с нуля, но к которым много обращений

Теперь закрепим знания из этого и предыдущего поста небольшой задачкой✍️

Есть 2 таблицы:

🔸 from_table со столбцами id, firstname, lastname.
🔸 to_table со столбцами id, name

Задача: перенести все строки из from_table в to_table, соединив firstname и lastname в одно поле name. После выполнения запроса from_table должен стать пустым.

Попробуйте выполнить задачу ОДНИМ запросом.

Онлайн Postgres: pgplayground

Исходный код для экспериментов:

CREATE TABLE from_table(id int, firstname text, lastname text);
INSERT INTO from_table VALUES(1, 'F1', 'L1');
CREATE TABLE to_table(id int, name text);

✨ РЕШЕНИЕ ✨

Вспоминаем, что DELETE возвращает удалённые строки. Формируем из них СТЕ и передаём в INSERT:

WITH deleted_rows AS (
   DELETE FROM from_table
   RETURNING id, firstname || ' ' || lastname
)
INSERT INTO to_table
   SELECT * FROM deleted_rows;