Defaults and Constraints not respected in duckdb storage extensions

[hafenkran]

What happens?

While migrating the duckdb-bigquery extension to the latest DuckDB main branch (as part of the Extension status toward v1.2.0 efforts), I noticed something odd that caused my tests to fail on all distributions. I figured out that the problem is also reproducible in other storage extensions (e.g., duckdb-postgres), so I'll focus on that as an example.

Issue: Defaults and constraints (e.g., NOT NULL) are no longer interpreted or displayed correctly. This happens regardless of whether the table already existed or was created by DuckDB. However, the defaults and constraints are correctly set in the respective database (e.g., Postgres, BigQuery).

To Reproduce

In the following DESCRIBE, the null column is expected to be NO, but is YES

D ATTACH 'dbname=testdb host=localhost port=5432 user=admin password=password' AS postgres_db (TYPE postgres);
D CREATE OR REPLACE TABLE postgres_db.public.test(s STRING NOT NULL);
D DESCRIBE postgres_db.public.test;
┌─────────────┬─────────────┬─────────┬─────────┬─────────┬─────────┐
│ column_name │ column_type │  null   │   key   │ default │  extra  │
│   varchar   │   varchar   │ varchar │ varchar │ varchar │ varchar │
├─────────────┼─────────────┼─────────┼─────────┼─────────┼─────────┤
│ s           │ VARCHAR     │ YES     │ NULL    │ NULL    │ NULL    │
└─────────────┴─────────────┴─────────┴─────────┴─────────┴─────────┘

I also reproduced this issue in the latest python nightly build using:

pip3 install --pre duckdb

import duckdb

conn = duckdb.connect(':memory:')  
describe_result = conn.execute("""
    ATTACH 'dbname=testdb host=localhost port=5432 user=admin password=password' AS postgres_db (TYPE postgres);

    CREATE OR REPLACE TABLE postgres_db.public.test (
        s STRING NOT NULL,
    );

    DESCRIBE postgres_db.public.test;        
""").fetchall()
print(describe_result)

Installing a non-nightly build and running the code above works as expected.

OS:

all

DuckDB Version:

duckdb-1.1.4.dev4271; or the latest main branch; the issue is also reproducible with a 2 weeks old duckdb commit

DuckDB Client:

CLI, Python

Hardware:

No response

Full Name:

hafenkran

Affiliation:

duckdb-bigquery project

What is the latest build you tested with? If possible, we recommend testing with the latest nightly build.

I have tested with a nightly build

Did you include all relevant data sets for reproducing the issue?

No - Other reason (please specify in the issue body)

Did you include all code required to reproduce the issue?

• Yes, I have

Did you include all relevant configuration (e.g., CPU architecture, Python version, Linux distribution) to reproduce the issue?

• Yes, I have

[Mytherin]

Thanks for reporting! This should be fixed by #287