postgres_scanner

postgres_scanner copied to clipboard

What happens?

duckdb cant perform an upsert on a postgresql table. Ive tried to run:

duckdb.sql(f"""
INSERT INTO postgres_db.old_dim ({', '.join(column_names)})
SELECT {', '.join(column_names)} FROM temp_rel
ON CONFLICT (pk_id)
DO UPDATE SET 
    name = EXCLUDED.name, 
    gender = EXCLUDED.gender,
    nationality = EXCLUDED.nationality,
    is_valid = EXCLUDED.is_valid,
    valid_to = EXCLUDED.valid_to;
""")

but the same error duckdb.duckdb.BinderException: Binder Error: The specified columns as conflict target are not referenced by a UNIQUE/PRIMARY KEY CONSTRAINT or INDEX keeps popping up even though i've made sure that pk_id is indeed a primary key in the postgres table

To Reproduce

duckdb.sql(f"""
INSERT INTO postgres_db.old_dim ({', '.join(column_names)})
SELECT {', '.join(column_names)} FROM temp_rel
ON CONFLICT (pk_id)
DO UPDATE SET 
    name = EXCLUDED.name, 
    gender = EXCLUDED.gender,
    nationality = EXCLUDED.nationality,
    is_valid = EXCLUDED.is_valid,
    valid_to = EXCLUDED.valid_to;
""")

OS:

Windows

DuckDB Version:

1.2.1

DuckDB Client:

Python

Hardware:

No response

Full Name:

Gahtan Syarif

Affiliation:

No affiliation

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

I have tested with a stable release

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

Not applicable - the reproduction does not require a data set

Did you include all code required to reproduce the issue?

• [x] Yes, I have

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

• [x] Yes, I have