I had a pretty busy week, so today I only have a short PostgreSQL tip I learned recently. Assume that you have a table of users, and it has two columns containing timestamps: created_at and deleted_at. The former is non-nullable, but the latter can contain nulls (for users who were never deleted). Let’s assume that you want to select all users who existed at some point in time, say today at midnight. Until recently, I would do something like this:
select *
from user
where
created_at <= '2026-01-26'
and ('2026-01-26' < deleted_at or deleted_at is null)
since, according to normal SQL rules, '2026-01-26' < null yields null and not false.
However, PostgreSQL (unlike the SQL standard) supports a few special values for dates and timestamps, and one of them is infinity. This means that I can combine it with the very useful coalesce function and write this:
select * from user where created_at <= '2026-01-26' and '2026-01-26' < coalesce(deleted_at, 'infinity')
(Of course, you might be tempted to use between, but please don’t do this.)
Of course, I could just use infinity as the default value for the deleted_at column, but this is definitely not a good practice.
That’s it for today, see you next week!