duckdb cant perform an upsert to postgresql table

[gahtan-syarif]

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?

• Yes, I have

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

• Yes, I have

[gahtan-syarif]

I dont believe this is an issue with my code as upserting from the postgresql table to temp_rel duckdb table works fine, its only when upserting from duckdb to postgres where the error occurs