Postgres: using 9999-12-31 vs NULL as expire date-CodePudding

In a Postgres table, I'm storing some events with their expire_date. I'm wondering how to model non-expiring events. Two alternatives:

The column expire_date will be indexed. From the performance perspective, are there any differences between the two approaches?

CodePudding user response：

If that column is used for range queries, storing NULL is a problem because you always need an OR condition which can be bad for performance.

But luckily, Postgres provides a DATE value that is bigger then all others: infinity so you can store that for the expire_date.

e.g.

insert into events (id, ..., expire_date)
values (1, ..., 'infinity');

Note that the opposite -infinity also exists. It also works for timestamp columns.

A range query on a NULL value could be indexed, if you use coalesce() instead of an OR condition:

create index on events (coalesce(expire_date, 'infinity'))

But you need to use that exact expression in your queries in order to make use of that index, e.g:

select *
from events
where coalesce(expire_date, 'infinity') > date '2022-08-01'

When storing infinity instead of NULL, you don't need to remember that in your queries.