drizzle-team/drizzle-orm · error · Error

Insert select error: selected fields are not the same or are

Error message

Insert select error: selected fields are not the same or are in a different order compared to the table definition

What it means

PgInsertBuilder.select (insert.ts:116) validates an INSERT...SELECT via haveSameKeys, comparing the table's declared columns against the subquery's selectedFields. Because the columns must line up positionally and by name for a Postgres INSERT...SELECT, a mismatch in count, name, or order is rejected up front. Only raw SQL objects bypass this check.

Source

Thrown at drizzle-orm/src/pg-core/query-builders/insert.ts:128

	}

	select(selectQuery: (qb: QueryBuilder) => PgInsertSelectQueryBuilder<TTable>): PgInsertBase<TTable, TQueryResult>;
	select(selectQuery: (qb: QueryBuilder) => SQL): PgInsertBase<TTable, TQueryResult>;
	select(selectQuery: SQL): PgInsertBase<TTable, TQueryResult>;
	select(selectQuery: PgInsertSelectQueryBuilder<TTable>): PgInsertBase<TTable, TQueryResult>;
	select(
		selectQuery:
			| SQL
			| PgInsertSelectQueryBuilder<TTable>
			| ((qb: QueryBuilder) => PgInsertSelectQueryBuilder<TTable> | SQL),
	): PgInsertBase<TTable, TQueryResult> {
		const select = typeof selectQuery === 'function' ? selectQuery(new QueryBuilder()) : selectQuery;

		if (
			!is(select, SQL)
			&& !haveSameKeys(this.table[Columns], select._.selectedFields)
		) {
			throw new Error(
				'Insert select error: selected fields are not the same or are in a different order compared to the table definition',
			);
		}

		return new PgInsertBase(this.table, select, this.session, this.dialect, this.withList, true);
	}
}

export type PgInsertWithout<T extends AnyPgInsert, TDynamic extends boolean, K extends keyof T & string> =
	TDynamic extends true ? T
		: Omit<
			PgInsertBase<
				T['_']['table'],
				T['_']['queryResult'],
				T['_']['selectedFields'],
				T['_']['returning'],
				TDynamic,
				T['_']['excludedMethods'] | K

View on GitHub (pinned to b7862528fd)

Solutions

1. Make the inner SELECT list exactly the destination table's columns in declaration order, keyed by the same names.
2. Prefer selecting the table object directly: qb.select().from(src) where src has identical columns, or map fields explicitly.
3. If you need a partial insert with defaults, use .values() instead of .select(), or build raw SQL to bypass the guard.
4. After schema changes, re-verify the column order of both source and destination.

Example fix

// before
db.insert(dest).select(db.select({ id: src.id }).from(src)); // missing cols -> error

// after
db.insert(dest).select(db.select({ id: src.id, name: src.name, email: src.email }).from(src)); // matches dest cols

import { getTableColumns } from 'drizzle-orm';

const srcColumns = Object.keys(getTableColumns(source)); // adjust to actual projection
const destColumns = Object.keys(getTableColumns(dest));
if (srcColumns.length !== destColumns.length
    || srcColumns.some((k, i) => k !== destColumns[i])) {
  throw new Error('Source projection must match destination columns by name and order');
}
await db.insert(dest).select(db.select().from(source));

function sameKeys(a: Record<string, unknown>, b: Record<string, unknown>): boolean {
  const ak = Object.keys(a), bk = Object.keys(b);
  return ak.length === bk.length && ak.every((k, i) => k === bk[i]);
}

When it happens

Trigger: db.insert(t).select(qb => ...) where the inner select picks fewer/more columns, columns in a different order, or aliases that don't match the table's column keys. Also triggered by selecting computed/aliased fields instead of raw table columns.

Common situations: Copying rows between tables with similar but not identical schemas; selecting a subset of columns while intending to rely on defaults; reordering a select to match a changed table definition; forgetting that selectedFields keys must equal the destination column keys.

Related errors

• Insert select error: selected fields are not the same or are
• Insert select error: selected fields are not the same or are
• You have an empty array for "${name}" enum values
• values() must be called with at least one value
• values() must be called with at least one value

AI-assisted analysis of drizzle-team/drizzle-orm@b7862528fd (2026-08-03). Data as JSON: /data/errors/873eb2b92f06e711.json. Report an issue: GitHub.