Прошлый пост собрал столько огоньков, что аж на душе потеплело, спасибо❤️
Продолжим наш ликбез по 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;