DuckDB allows you to insert into table with mismatching column names

[Admolly]

What happens?

Normally, if you insert rows into a table where the column names do not match, DuckDB will throw an error. However, If I insert rows into a table using the INSERT INTO table SELECT ... method, the command will not fail so long as the data types match or can be coerced. I'm not sure if this is a bug or by design, but to me it would be helpful if it were to throw an error here, at least to be consistent with the other method.

To Reproduce

import duckdb
con = duckdb.connect()
con.execute("create table t1 as select 1 as id") 
con.execute("insert into t1 select 2 as other_id") # does not fail
con.execute("insert into t1 (other_id) VALUES (3)") # Binder Error: Table "t1" does not have a column with name "other_id"

OS:

Windows 10

DuckDB Version:

0.7.2-dev225

DuckDB Client:

Python

Full Name:

Vincent Munos

Affiliation:

Admolly

Have you tried this on the latest master branch?

• I agree

Have you tried the steps to reproduce? Do they include all relevant data and configuration? Does the issue you report still appear there?

• I agree

[Mytherin]

This is not a bug but by design (of the SQL standard). SQL inserts operate on a positional basis, any names in the SELECT list are ignored. We might consider adding an INSERT INTO t1 BY NAME or similar option.

[Admolly]

Thanks for the clarification

[Tishj]

Closing as this is intended behavior
(And addressed by the new BY NAME insert option)