Witam na mojej prywatnej stronie internetowej!
[If this is all Polish to you, click here: English]
Uwaga: z oczywistych powodów nie mogę zagwarantować swojej nieomylności, choć staram się o zgodność tego, co piszę, z Prawdą. Jest również oczywiste, że nie gwarantuję takiej zgodności w przypadku komentarzy. Umieszczenie linku do strony spoza niniejszego serwisu nie musi oznaczać, że podzielam poglądy autora tej strony, a jedynie, że uważam ją za wartościową z takich czy innych powodów.
Marcin ‘mbork’ Borkowski
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!