Temporal Extensions
Postgres can be extended to become a Temporal Database. Such databases track the history of database content over time, automatically retaining said history and allowing it to be altered and queried. This functionality is useful when asking what the database content looked like, what the database content should have looked like to reflect actuality, or what the officially recognized facts were even though database updates had not yet been made, at any point in time. The first 2 by themselves being uni-temporal, transaction time and valid time respectively, the first 2 together being bi-temporal, and simultaneous support for all 3 being tri-temporal.
List of Extensions and Temporal Enhancements
| Name | Description | Implementation | Uni-Temporal | Bi-Temporal | Tri-Temporal | Notes |
|---|---|---|---|---|---|---|
| Periods | Periods and SYSTEM VERSIONING for PostgreSQL | Extension | Y | Y | Y | This extension recreates the behavior defined in SQL:2023 (originally in SQL:2011) around periods and tables with SYSTEM VERSIONING. It has a SQL-standard query syntax, as far as possible. |
| Temporal Tables Extension | Support for temporal tables. | Extension | Transaction time | N | N | When you update or delete a row from a system-period temporal table, the old row is archived into another table, which is called the history table. |
| Temporal Tables | Rewrite of Temporal Tables Extension as a trigger | Before Trigger | Transaction time | N | N | This rewrite aims to provide a temporal tables solution in PL/pgSQL, targeting AWS RDS, Google Cloud SQL, and Azure Database for PostgreSQL where custom C extensions aren't permitted. |
| pg_bitemporal | Bi-Temporal Extension | Extension | ? | Y | ? | Sophisticated, but the documentation consists of Chicago PUG 2020 and Chicago 2QPG (2017?) presentation videos & related slide shows. |
PostgreSQL Features Supporting Temporality
Types
- TIMESTAMP - already in PostgreSQL
- INTERVAL - already in PostgreSQL
- PERIOD
- PERIOD(DATE)
- PERIOD(TIME) and PERIOD(TIME WITH TIMEZONE)
- PERIOD(TIMESTAMP) and PERIOD(TIMESTAMP WITH TIMEZONE)
- PERIOD(INT) and PERIOD(INTEGER)
- PERIOD(SMALLINT)
- PERIOD(NUMERIC) - only with scale 0
- PERIOD(DECIMAL) - only with scale 0
Since the TIMESTAMP and INTERVAL types are in PostgreSQL, this information will focus on the PERIOD types.
Constructors
...
Operators
In the following operators, p and q are PERIODs and i is an INTERVAL.
- p = q
- p PRECEDES q
- p SUCCEEDS q
- ...
Resources
* initial RFC - contains a lot of details and further references
