Краткий ответ

CREATE TABLE ... AS SELECT создаёт новую таблицу на основе результата запроса, но не гарантирует перенос ограничений целостности исходной таблицы. В частности, автоматически не следует рассчитывать на сохранение первичного ключа, внешних ключей, уникальных ограничений, проверок, значений по умолчанию и связанных индексов.

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

Исторический контекст

Конструкция CREATE TABLE AS SELECT появилась как средство быстро материализовать результат запроса в отдельный объект схемы. Она особенно полезна для промежуточных наборов данных, отчётных срезов, ETL-операций и архивов.

Её цель — создать таблицу с колонками, совместимыми с результатом запроса, а не клонировать исходную таблицу со всей метаинформацией. Полное копирование структуры требовало бы отдельного описания ограничений, индексов, триггеров и других объектов.

Постановка проблемы

В примере новая таблица получает столбцы и строки, возвращённые запросом. Однако наличие столбца order_id не означает автоматически наличие первичного ключа, а наличие customer_id не создаёт внешний ключ на таблицу клиентов.

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

Подробное решение

CREATE TABLE AS SELECT выводит структуру новой таблицы из схемы результата SELECT: имена столбцов, их типы и значения формируются по выражениям запроса. Тип столбца может зависеть не только от исходного столбца, но и от приведений типов, функций, арифметики и условных выражений.

Ограничения и служебные объекты нужно задавать отдельно. Например:

CREATE TABLE archive.orders_2024 (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    total DECIMAL(12, 2) NOT NULL
);

INSERT INTO archive.orders_2024 (order_id, customer_id, total)
SELECT order_id, customer_id, total
FROM sales.orders
WHERE created_at < DATE '2025-01-01';

CREATE INDEX orders_2024_customer_idx
    ON archive.orders_2024 (customer_id);

Такой вариант даёт явный контроль над типами и ограничениями, но требует поддержки определения таблицы при изменении исходной модели. Альтернатива — сначала выполнить CREATE TABLE AS, а затем добавить нужные ограничения и индексы через ALTER TABLE; это быстрее для разовой операции, но необходимо отдельно проверить уже загруженные данные.

Конкретный перечень автоматически переносимых свойств может различаться между СУБД и режимами команды. Поэтому перенос ограничений нельзя считать переносимым свойством SQL-кода без проверки документации целевой СУБД.

Ситуация из практики

Команда создавала ежедневный архив заказов через CREATE TABLE AS SELECT, после чего аналитики обнаружили повторяющиеся order_id. Рассматривались два варианта: оставить CTAS и добавить уникальный индекс после загрузки либо заранее описать таблицу с первичным ключом и выполнить INSERT.

Первый вариант проще для разового неизменяемого снимка, но добавление уникального индекса может завершиться ошибкой, если дубликаты уже попали в архив. Второй вариант требует больше DDL-кода, зато нарушение ключа выявляется во время загрузки и структура таблицы становится явно документированной.

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

Что кандидаты часто упускают

Перенесёт ли CTAS первичный ключ, если выбранный столбец называется так же, как в исходной таблице?

Нет, совпадение имени не переносит семантику ключа. Первичный ключ — это ограничение объекта таблицы, а не свойство имени или типа столбца. Если уникальность нужна в новой таблице, её задают явно через PRIMARY KEY или UNIQUE, предварительно проверив существующие данные.

Что произойдёт с типом столбца, если в SELECT используется выражение?

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

Можно ли считать архив корректным, если ограничения не перенесены, но исходные данные уже прошли проверку?

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