Waiting for PostgreSQL 18 – Add temporal PRIMARY KEY and UNIQUE constraints

Generally, thanks to this patch we can now track changes in rows, in time, and thanks to another patch that I will cover shortly, keep foreign keys pointed to appropriate row version.

You can, of course, have orders.address_id int8 references addresses (id), but what will happen if someone that already made an order, would then change their address, and make another order.

Of course, there are many solutions to this problem. One could for example, always make new rows, and just make previous version inactive.

If I'd need to update the address, I insert new version, and change range for the previous one. Well, the other way around 🙂

And since I can't have two rows, with the same id, that overlapr (in terms of valid_range) – given any timestamp, I can tell which version of the row was valid at the time.

This is great. Maybe not for every application there is, but it's definitely very helpful for whenever you have to track changes, and be able to show how the row looked like at any point in time – this will be godsend.

Leave a Comment