Вставка данных в PostgreSQL с подзапросом из другой таблицы

Быстрый ответ

INSERT INTO target (col1, col2)
SELECT col1, col2 FROM source WHERE criteria;

Ниже приведен пример команды INSERT с вложенным подзапросом для переноса данных из source в таблицу target, удовлетворяющих условию criteria. Подобный подход позволяет выполнить перенос данных одним запросом.

Расширенное рассмотрение вложенных подзапросов в INSERT

При внедрении данных через подзапрос следует учитывать, что структура столбцов в таблицах source и target должна быть согласованной. Это позволяет избегать ошибок и препятствует неправильной перестановке данных.

Вставка констант? Легко и просто!

Если в рамках ваших транзакций предполагается использование как динамических, так и статических значений, PostgreSQL предлагает простое решение:

-- Важно: После выбора цвета 'Синий', изменить его уже не получится!
INSERT INTO widgets (widget_id, widget_name, color)
SELECT DEFAULT, widget_name, 'Blue' FROM source WHERE condition;

В данном случае для поля widget_id назначается автоматически увеличивающееся значение DEFAULT, в то время как 'Синий' выполняет роль константы для всех записей.

Подробнее об этом расскажет наш спикер на видео

Агрегация данных "на лету"

Взять и агрегировать данные прямо в процессе внесения их в таблицу можно вот так:

-- "Ежедневные сводные обновления – для тех, кто не успевает читать часовые отчеты!"
INSERT INTO sales_summary (sale_date, total_sales)
SELECT TODAY, SUM(amount) FROM sales WHERE sale_date = TODAY;

Данный запрос суммирует все сегодняшние продажи и вносит их в таблицу sales_summary.

Множество строк? Не вопрос!

PostgreSQL с легкостью вставит множество строк, которые будут возвращены подзапросом:

-- "Одобрено вам! Одобрено вам! Одобрено всем!"
INSERT INTO project (project_name, start_date)
SELECT name, start_date FROM proposals WHERE approved;

Каждое одобренное предложение будет трансформировано в запись в таблице project.

Визуализация 📊

Можно представить вложенный подзапрос операции INSERT как конвейер: команда INSERT открывает "двери" для данных, а SELECT выполняет роль конвейерной ленты, переносящей данные из исходной таблицы в целевую.

-- "Добавление в... словно контроль безопасности в аэропорту: определяет, что проходит, а что не проходит!"
INSERT INTO wall (brick_id, color, strength)
SELECT brick_id, color, strength FROM bricks_stock WHERE color = 'red' AND strength > 5;

Только данные, соответствующие условиям подзапроса, попадут в целевую таблицу.

Когда использование подзапросов может быть неоптимальным

Несмотря на их эффективность, существуют сценарии, когда подзапросы могут быть неоптимальными:

• Производительность: Для обработки больших объемов данных подзапросы могут работать медленнее, чем массовые вставки данных.
• Сложность подзапросов: Если составление подзапроса представляется излишне сложным, стоит рассмотреть возможность использования Common Table Expressions (CTE).
• Проблемы с блокировками: Вложенные подзапросы могут вызвать непреднамеренные блокировки исходных таблиц.

Советы для профессионального использования подзапросов

• Явно укажите столбцы в целевой таблице, чтобы защититься от изменений схемы и возможных ошибок.
• Воспользуйтесь псевдонимами для упрощения работы с несколькими таблицами и вложенными подзапросами.
• Для ускорения запросов проиндексируйте те поля в исходной таблице, которые используются в условии WHERE.

Полезные материалы

1. PostgreSQL: Documentation: 16: INSERT — Официальное руководство по использованию INSERT в PostgreSQL.
2. SQL – Insert into ... values (SELECT ... FROM ...) — Обсуждение на StackOverflow о применении команды INSERT с подзапросами.
3. PostgreSQL: Documentation: 16: 7.8. WITH Queries (Common Table Expressions) — Официальная документация по использованию Общих Табличных Выражений (CTE).
4. PostgreSQL's CTEs are optimization fences – 2ndQuadrant | PostgreSQL — Анализ влияния CTE на производительность запросов в PostgreSQL.