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

Thrown by MySqlInsertBuilder.select (line 109) when an INSERT...SELECT is built and the select query's selected fields don't match the table's columns exactly (count and order). haveSameKeys enforces that the SELECT projects the same keys in the same order as the target table's columns.

Source

Thrown at drizzle-orm/src/mysql-core/query-builders/insert.ts:109

	select(
		selectQuery: (qb: QueryBuilder) => MySqlInsertSelectQueryBuilder<TTable>,
	): MySqlInsertBase<TTable, TQueryResult, TPreparedQueryHKT>;
	select(selectQuery: (qb: QueryBuilder) => SQL): MySqlInsertBase<TTable, TQueryResult, TPreparedQueryHKT>;
	select(selectQuery: SQL): MySqlInsertBase<TTable, TQueryResult, TPreparedQueryHKT>;
	select(selectQuery: MySqlInsertSelectQueryBuilder<TTable>): MySqlInsertBase<TTable, TQueryResult, TPreparedQueryHKT>;
	select(
		selectQuery:
			| SQL
			| MySqlInsertSelectQueryBuilder<TTable>
			| ((qb: QueryBuilder) => MySqlInsertSelectQueryBuilder<TTable> | SQL),
	): MySqlInsertBase<TTable, TQueryResult, TPreparedQueryHKT> {
		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 MySqlInsertBase(this.table, select, this.shouldIgnore, this.session, this.dialect, true);
	}
}

export type MySqlInsertWithout<T extends AnyMySqlInsert, TDynamic extends boolean, K extends keyof T & string> =
	TDynamic extends true ? T
		: Omit<
			MySqlInsertBase<
				T['_']['table'],
				T['_']['queryResult'],
				T['_']['preparedQueryHKT'],
				T['_']['returning'],
				TDynamic,
				T['_']['excludedMethods'] | '$returning'

View on GitHub (pinned to b7862528fd)

Solutions

1. Select exactly the table's columns in the same order: select every column from the source by its table key name.
2. If you need a subset, use a separate insert().values() path or align the source table schema to match.
3. Ensure aliases in the select match the target table column keys (haveSameKeys compares Object.keys order).
4. For raw SQL, make sure the projection yields the same field set/order as the table definition.

Example fix

// before - subset/mismatch
db.insert(users).select(
  qb => qb.select({ id: source.id, name: source.name }).from(source)
); // users has more columns => throws

// after - select all columns in order
db.insert(users).select(
  qb => qb.select({ id: source.id, name: source.name, email: source.email, createdAt: source.createdAt }).from(source)
);

import { haveSameKeys, Columns, Table } from '~/utils.ts';
// Compare selected fields keys to table columns keys before insert().select()
const ok = haveSameKeys(table[Columns], selectQuery._.selectedFields);
if (!ok) throw new Error('Select projection must match target table columns in order');

function projectionMatchesTable(table: MySqlTable, sel: { getSelectedFields(): Record<string, unknown> }): boolean {
  return haveSameKeys(table[Columns], sel.getSelectedFields());
}

When it happens

Trigger: db.insert(table).select(qb => qb.select({ ... }).from(...)) where the selected fields differ in number, names, or order from table[Columns]. Also fires for a raw SQL select if it's not a TypedQueryBuilder with matching selectedFields.

Common situations: Selecting a subset of columns; selecting columns in a different order than defined in the table; aliasing columns differently than the table's keys; forgetting computed/generated columns.

Related errors

• values() must be called with at least one value
• Insert select error: selected fields are not the same or are
• You have an empty array for "${name}" enum values
• Cannot pass undefined values to any set operator
• Alias "${tableName}" is already used in this query

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