Ability to insert default value when using db.table(...).insert(...)

[frankier]

It does not currently seem possible to use insert(...) from the relational API https://duckdb.org/docs/stable/clients/python/relational_api#insert together with default column values.

Here's some ways it could work:

import duckdb
import traceback


db = duckdb.connect("mydb.db")
db.execute("""
create sequence id_seq start 1;

create table cool_numbers (
    id integer default nextval('id_seq') primary key,
    number int
);
""")

try:
    db.table("cool_numbers").insert((42,))
except Exception:
    print(traceback.format_exc())

try:
    db.table("cool_numbers").insert((None, 42))
except Exception:
    print(traceback.format_exc())

try:
    db.table("cool_numbers").select("number").insert((42,))
except Exception:
    print(traceback.format_exc())

Here's the output:

Traceback (most recent call last):
  File "/home/frankier/edu/rse/apitofsim/duckdbinsertpls.py", line 16, in <module>
    db.table("cool_numbers").insert((42,))
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^^^^^^^
_duckdb.BinderException: Binder Error: table cool_numbers has 2 columns but 1 values were supplied

Traceback (most recent call last):
  File "/home/frankier/edu/rse/apitofsim/duckdbinsertpls.py", line 21, in <module>
    db.table("cool_numbers").insert((None, 42))
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^^^^^^^^^^^^
_duckdb.ConstraintException: Constraint Error: Failed to insert into table 'cool_numbers': NOT NULL constraint failed: cool_numbers.id

Traceback (most recent call last):
  File "/home/frankier/edu/rse/apitofsim/duckdbinsertpls.py", line 26, in <module>
    db.table("cool_numbers").select("number").insert((42,))
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^^^^^^^
_duckdb.InvalidInputException: Invalid Input Error: 'DuckDBPyRelation.insert' can only be used on a table relation

Rather than using None, there could be some sort of duckdb.DEFAULT sentinel.