`DESCRIBE` a table in Postgres does not get not null and primary key constraints

What happens?

When you create table with a primary key to Postgres from DuckDB and then DESCRIBE it, not null and primary key constraints are shown. The constraints are also present when you check the table directly in Postgres. Then, after you DETACH, ATTACH, and DESCRIBE again, the constraints are no longer shown.

Came across this while trying to upsert to Postgres which I now found is not supported yet https://github.com/duckdb/postgres_scanner/issues/189. Is this also expected behavior?

To Reproduce

docker compose up the following docker-compose.yaml:

services:
  postgres:
    image: postgres:16.2-alpine
    ports:
      - 5432:5432
    volumes:
      - pg_vol:/var/lib/postgresql/data
    environment:
      - POSTGRES_USER=airflow
      - POSTGRES_PASSWORD=airflow
      - POSTGRES_DB=airflow

volumes:
  pg_vol:

Then, run the following Python script:

import duckdb

duckdb.sql("INSTALL postgres")
duckdb.sql("LOAD postgres")
duckdb.sql("ATTACH 'postgresql://airflow:airflow@localhost:5432/airflow' AS pg (TYPE postgres)")

duckdb.sql("CREATE OR REPLACE TABLE pg.public.upsert_test_table (col1 INTEGER PRIMARY KEY, col2 VARCHAR)")
duckdb.sql("DESCRIBE pg.public.upsert_test_table").show()
duckdb.sql("DETACH pg")

duckdb.sql("ATTACH 'postgresql://airflow:airflow@localhost:5432/airflow' AS pg (TYPE postgres)")
duckdb.sql("DESCRIBE pg.public.upsert_test_table").show()

The output is

┌─────────────┬─────────────┬─────────┬─────────┬─────────┬─────────┐
│ column_name │ column_type │  null   │   key   │ default │  extra  │
│   varchar   │   varchar   │ varchar │ varchar │ varchar │ varchar │
├─────────────┼─────────────┼─────────┼─────────┼─────────┼─────────┤
│ col1        │ INTEGER     │ NO      │ PRI     │ NULL    │ NULL    │
│ col2        │ VARCHAR     │ YES     │ NULL    │ NULL    │ NULL    │
└─────────────┴─────────────┴─────────┴─────────┴─────────┴─────────┘

┌─────────────┬─────────────┬─────────┬─────────┬─────────┬─────────┐
│ column_name │ column_type │  null   │   key   │ default │  extra  │
│   varchar   │   varchar   │ varchar │ varchar │ varchar │ varchar │
├─────────────┼─────────────┼─────────┼─────────┼─────────┼─────────┤
│ col1        │ INTEGER     │ YES     │ NULL    │ NULL    │ NULL    │
│ col2        │ VARCHAR     │ YES     │ NULL    │ NULL    │ NULL    │
└─────────────┴─────────────┴─────────┴─────────┴─────────┴─────────┘

OS:

Ubuntu 20.04.6 (GitHub Codespaces default image mcr.microsoft.com/devcontainers/universal:linux)

PostgreSQL Version:

16.2

DuckDB Version:

0.10.0 and 0.10.1.dev1244

DuckDB Client:

Python

Full Name:

Ville Puuska

Affiliation:

Solita

Have you tried this on the latest `main` branch?

[X] 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?

[X] I agree

duckdb / postgres_scanner