ProgrammingError - sqlalchemy - on_conflict_do_update

Question

Following this question: As Ilja Everilä mentioned in his answer, I created a table object: ``` from sqlalchemy import * metadata = MetaData() idTagTable = Table('id_tag', metadata, Column('id', String(255), primary_key = True), Column('category', String(20), nullable = False), Column('createddate', Date, nullable = False), Column('updatedon', Date, nullable = False) ) ``` After creating a table object, I changed insert and update statements: ``` insert_statement = sqlalchemy.dialects.postgresql.insert(idTagTable) upsert_statement = insert_statement.on_conflict_do_update( constraint=PrimaryKeyConstraint('id'), set_={"updatedon": insert_statement.excluded.updateon, "category":insert_statement.excluded.category} ) insert_values = df.to_dict(orient='records') conn.execute(upsert_statement, insert_values) ``` Now I am getting Programming Error: ``` Traceback (most recent call last): File "", line 7, in conn.execute(upsert_statement, insert_values) F…

Accepted Answer

The `PrimaryKeyConstraint` object you're using as `constraint=` argument is not bound to any table and would seem to produce nothing when rendered, as seen in `ON CONFLICT ()`. Instead pass the primary key(s) of your table as the conflict_target and Postgresql will perform unique index inference:

```
upsert_statement = insert_statement.on_conflict_do_update(
        constraint=idTagTable.primary_key,
        set_={"updatedon": insert_statement.excluded.updateon,
              "category":insert_statement.excluded.category}
)
```

ProgrammingError - sqlalchemy - on_conflict_do_update

Solution

Problem