В архив переносят заказы, но порядок столбцов в источнике отличается от порядка столбцов целевой таблицы. К...

CREATE TABLE archive (
    order_id INTEGER,
    total NUMERIC
);

CREATE TABLE source (
    total NUMERIC,
    order_id INTEGER
);

INSERT INTO archive
SELECT total, order_id
FROM source;

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

Без списка столбцов после имени таблицы значения из SELECT сопоставляются с целевыми столбцами по позиции, а не по именам. В примере total будет записан в archive.order_id, а order_id — в archive.total; запрос завершится ошибкой типов или создаст некорректные данные, если типы совместимы.

Безопасный вариант явно задаёт соответствие:

INSERT INTO archive (order_id, total)
SELECT order_id, total
FROM source;

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

Табличная модель SQL представляет строку как набор значений, упорядоченных относительно схемы таблицы. Поэтому операция INSERT должна иметь однозначное правило сопоставления результата запроса со столбцами целевой таблицы.

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

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

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

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

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

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

В примере первая позиция результата содержит source.total, поэтому она направляется в первый столбец archive, то есть в archive.order_id. Вторая позиция содержит source.order_id и направляется в archive.total.

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

INSERT INTO archive (order_id, total)
SELECT order_id, total
FROM source;

Список целевых столбцов также позволяет не указывать столбцы, для которых применяются значения по умолчанию или допускается NULL. Однако количество выбранных выражений должно соответствовать количеству перечисленных целевых столбцов, а значения должны быть приводимы к их типам и удовлетворять ограничениям NOT NULL, CHECK, UNIQUE и внешним ключам.

Важно отличать это от INSERT ... VALUES: там действует то же позиционное правило. Кроме того, SELECT * делает контракт особенно хрупким: изменение схемы источника может изменить количество и порядок возвращаемых столбцов. Для прикладного кода обычно предпочтительны явные списки столбцов с обеих сторон.

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

Ночной процесс переносил данные из orders в orders_archive. Разработчик добавил в исходную таблицу столбец discount и изменил порядок выражений в SELECT, но оставил INSERT INTO orders_archive SELECT ... без списков столбцов. Типы discount и другого числового поля были совместимы, поэтому загрузка завершилась без ошибки, но отчёты получили неверные суммы.

Рассматривались три варианта. Сохранить SELECT * было проще всего, но это оставляло зависимость от схемы. Использовать позиционный SELECT без списка назначения было немного короче, но сохраняло риск перестановки. Явно перечислить столбцы в INSERT и SELECT потребовало небольшого изменения кода, зато сделало соответствие проверяемым при ревью.

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

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

1. Что произойдёт, если в INSERT указаны не все столбцы целевой таблицы?

   Неуказанные столбцы получают значение по умолчанию, если оно задано; иначе используется NULL, если столбец допускает NULL. Если столбец объявлен NOT NULL без подходящего значения по умолчанию, вставка завершается ошибкой. Поэтому явный список не только защищает от перестановки, но и позволяет намеренно использовать значения по умолчанию.

2. Защищает ли список столбцов в INSERT от ошибок в порядке выражений SELECT?

   Нет. Он фиксирует порядок столбцов назначения, но сами выражения SELECT всё равно сопоставляются с перечисленными столбцами позиционно. Например, INSERT INTO archive (order_id, total) SELECT total, order_id ... по-прежнему пытается записать total в order_id. Надёжный контракт требует явно проверить оба списка и их смысловой порядок.

3. Можно ли полагаться на имена столбцов при INSERT ... SELECT?

   Нет, имена не выполняют автоматическое сопоставление. Даже если выражение SELECT имеет алиас order_id, его положение в списке остаётся определяющим. Чтобы сопоставление было корректным, нужно явно расположить выражения в порядке целевых столбцов и проверить преобразования типов и ограничения таблицы.