Date: 2026-05-21
Time: 11:00–11:50
Room: Canfor
Level: Intermediate
What temporal features does Postgres support from SQL:2011, what remains to be done, and what can we imagine beyond the standard?
In v18 we got temporal primary keys, unique constraints, and foreign keys. There are patches being reviewed for temporal update/delete. (Maybe by May they will be committed.) With that functionality, foreign keys can support CASCADE/SET NULL/SET DEFAULT. There is a less-mature patch to add PERIODs. System time is also being developed. I'll briefly cover all these features.
But there is a lot needed the standard doesn't cover. Most of my talk will discuss what I see missing and how I'd like to approach it: what can core do? What can be an extension? What should we advocate to standarize? A big gap is missing join types (outer join, antijoin, semijoin) and other missing operators (UNION, INTERSECT, EXCEPT, aggregates). If we implement something in core, we need to consider algebraic equivalences and planner transformations for temporal operators. Another big feature is temporal MERGE or ON CONFLICT DO UPDATE: something that updates history while filling in missing gaps. A small thing I'd like to achieve is making all these application-time features extensible enough to work with user-defined types. We are almost there, but it needs something else like a "type support function". I'm also interested in allowing multiple dimensions of application-time: some researchers have proposed using this to model truth claims. An out-of-core extension for multi-dimensional rangetypes could provide this.
Patch for temporal update/delete: https://commitfest.postgresql.org/patch/5836/
SQL implementations for missing temporal operators: https://github.com/pjungwir/temporal_ops