Unique index w/ nullable field in unique query
I'm having an issue with a unique index where one of the fields is a nullable field (deleted at)
When attempting to findUnique or upsert with the unique index, I'm unable to use it with the value of null (which should get me the only row that's not deleted if there's one). Instead, it errors, saying that the value must not be null and must be a proper date
I'm not really sure how to go around this, since I can't separate the where clause to name and deletedAt since I'm required to use unique field(s)
2 Replies
I’m not sure this is possible, as unique indexes with nulls aren’t supported. I think that linked issue has some suggestions
GitHub
Issues · prisma/prisma
Next-generation ORM for Node.js & TypeScript | PostgreSQL, MySQL, MariaDB, SQL Server, SQLite, MongoDB and CockroachDB - Issues · prisma/prisma
Oh right,
We were trying to cheat the behavior of
Unique Index ... where ...
Since I don't think it's supported either and we missed this
Thanks for the support 🙂
Is there any way to get the intended behavior?
Trying to rewrite the generated sql migration to add the where clause to the unique index will just re-attemlt to generate the original index each time we run the migrations and unsyncs the db and the schema
The only way I managed to make it work was by removing the unique index entirely from the schema and create it via an empty migration, but then it won't be as obvious that it exists when looking at the schema, and the client won't provide the option to use the constraint in queries
What about the nulls not distinct option?
Forgot to mention I'm using a postgress db