Upsert Limitaion for Partial Index

[ojha007]

Hey everyone 👋

I ran into a limitation with upsert() when working with PostgreSQL partial indexes and wanted to check if others have encountered this.

When using a partial unique index (e.g., one with a WHERE clause), calling upsert() with the corresponding columns in uniqueBy can result in an error like:

ERROR: There is no unique or exclusion constraint matching the ON CONFLICT specification

This seems to happen because the generated ON CONFLICT clause doesn’t include the condition from the partial index, so it doesn’t match the index definition.

CREATE UNIQUE INDEX example_unique_index ON example_table (column_a, column_b) WHERE column_b IS NOT NULL;

INSERT INTO example_table (column_a, column_b) VALUES ('value', 'value')
ON CONFLICT (column_a, column_b) WHERE column_b IS NOT NULL
DO UPDATE SET updated_at = EXCLUDED.updated_at;

At the moment, the main workaround is falling back to raw queries, which means losing some of the conveniences of the query builder and model layer.

Would it make sense to support something like an additional parameter (e.g., a way to specify conditions for the conflict target) so partial indexes can be used more naturally with upsert()?

Curious if others have run into this or have alternative approaches. Happy to open a formal feature request if this sounds reasonable.