Intervals: How can I make sure there is just one row with a null value in a timstamp column in table?

Question

I have a table with a column which contains a 'valid until' Date and I want to make sure that this can only be set to null in a single row within the table. Is there an easy way to do this?

My table looks like this (postgres):

CREATE TABLE 123.myTable(
some_id integer NOT NULL,
valid_from timestamp without time zone NOT NULL DEFAULT now(),
valid_until timestamp without time zone,
someString character varying)

some_id and valid_from is my PK. I want nobody to enter a line with a null value in column valid_until if there is already a line with null for this PK.

Thank you

Answer 1

In PostgreSQL, you have two basic approaches.

1. Use 'infinity' instead of null. Then your unique constraint works as expected. Or if you cannot do that:
2. CREATE UNIQUE INDEX null_valid_from ON mytable(someid) where valid_until IS NULL

I have used both approaches. I find usually the first approach is cleaner and it allows you to use range types and exclude constraints in newer versions of PostgreSQL better (to ensure no two time ranges overlap based on a given given someid), bt the second approach often is useful where the first cannot be done.