I have a table called Person with three fields - ID, city and name. City can be null, so I have two partial unique indexes - one on ID and city where city is not null, and one on ID where city is null.
Now I want to have an upsert statement using SQLAlchemy, with those partial indexes, but I can't figure out the syntax. Currently I have:
table = models.Person.__table__
insert_statement = insert(table, upsert_values)
update_dict = {c.name: c for c in insert_statement.excluded if c.name != "id"}
upsert_statement = insert_statement.on_conflict_do_update(
index_elements=[table.c['id'], table.c['city']],
set_=update_dict
)
but when I try to execute I get
sqlalchemy.exc.ProgrammingError: (psycopg2.errors.InvalidColumnReference) there is no unique or exclusion constraint matching the ON CONFLICT specification
How can this work? Is there a way other than running pure SQL?
