Skip to content

(Ab)Using Null Uniqueness in Postgres

2 pointsBWStearnsdiscuss
On HN

So I have a pg table of requests where I want a composite key of two columns `(A, B)` where column A should be unique if column B is not null. A is a timestamp, B is just a flag for whether or not it's been canceled. The point of this is to make sure only one uncanceled request can exist for a given time.

I've solved this (clumsily?) by making B a bool and the only values are T and Null. This feels real dirty since F is a possible but irrelevant value, but I'm basically trying to hack in a dependent type check by exploiting the null uniqueness semantics of postgres.

Does anyone have a good suggestion for a cleaner alternative? I kind of wish there were a nullable unit type in PG.

Comments

No comments yet.

AboutSource Built by g1lg1l

Hackerly is an independent reader for Hacker News, built on the public HN API. Not affiliated with Y Combinator.