GoKawiilRecently, a new type of question has entered the database arena: what did this data look like last Tuesday? Maybe it's the price of a product before the holiday sale kicked in, or which department an employee belonged to before that reorg nobody asked for. Short of adding an entire audit trigger system, how can we know what data looked like before and after a change at that exact date?
The SQL:2011 standard formalized a proper solution over a decade ago with temporal tables. Other database engines adopted pieces of it relatively quickly. Characteristically, Postgres took its time. But Postgres 19 is finally bringing native temporal table support to the party — and it's been well worth the wait.
Let’s see what we’re working with.
The Old-Fashioned Way
Before we get to the shiny new stuff, let’s check out the crusty old approach for some perspective. Suppose we want to track product pricing over time. A reasonable first attempt might look like this:
CREATE EXTENSION IF NOT EXISTS btree_gist; CREATE TABLE products ( product_id INT NOT NULL, product_name TEXT NOT NULL, price NUMERIC(10,2) NOT NULL, valid_from DATE NOT NULL, valid_to DATE NOT NULL, CONSTRAINT no_time_travel CHECK (valid_from < valid_to) );
Simple enough. We've got a product, a price, and a valid date range for the price. Unfortunately, nothing stops us from inserting two rows for the same product with overlapping date ranges. Product number 42 could be $9.99 and $14.99 on the same Tuesday. Your accountant might have some choice words upon discovering that.
The traditional Postgres answer here is the btree_gist extension and an exclusion constraint:
ALTER TABLE products ADD CONSTRAINT no_overlapping_prices EXCLUDE USING gist ( product_id WITH =, daterange(valid_from, valid_to) WITH && );
This works. If we try to insert a conflicting row, Postgres catches it:
... continue reading