[BUG]: "Insert into ... select" expects all columns

[sohrab-]

Report hasn't been filed before.

• I have verified that the bug I'm about to report hasn't been filed before.

What version of drizzle-orm are you using?

0.36.4

What version of drizzle-kit are you using?

N/A

Other packages

No response

Describe the Bug

Using the new "Insert into... select" feature, the implementation seems to expect all table columns to be present in the select. Here is the relevant part in the source code. In SQL, this is not required and honestly in many cases undesirable. I am not sure if the current implementation would work for any real-life example...

Here is a reproduction repo: https://stackblitz.com/edit/stackblitz-starters-ll4899?file=index.js

If you run this, it will fail with the following:

Error: Insert select error: selected fields are not the same or are in a different order compared to the table definition
    at PgInsertBuilder.select (/home/projects/stackblitz-starters-ll4899/node_modules/drizzle-orm/pg-core/query-builders/insert.cjs:86:13)

I believe the corresponding SQL statement is valid and executable. You don't really want to explicitly supply id in this use case.

insert into transaction (type, user_id)
select 'Stuff' as type, user_id
from user
where user.name = 'Bob';

I think this is an issue across all supported dialects.

PS. The documentation examples seem to be inserting 1-2 columns at most so that's kinda misleading given the above.

I raised this on Discord but I think it got lost amongst all the messages.

[czystyl]

I was looking for id workaround and this is what was working for me:

id: sql<number>`nextval('TABLE_NAME_id_seq'::regclass)`.as("id"),

[sohrab-]

I am not sure what dialect you are using but in PostgreSQL, you can do something like this:

id: sql`default`

Drizzle actually does this on other interactions internally coz it likes to enumerate all of the columns. I am not sure why this was not implemented for "insert into ... select" specifically and forcing the consumer to do the column enumeration.