{"id":16671,"date":"2021-08-26T14:00:38","date_gmt":"2021-08-26T12:00:38","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/temporal-tables-for-postgresql-15\/"},"modified":"2021-08-26T14:00:38","modified_gmt":"2021-08-26T12:00:38","slug":"temporal-tables-for-postgresql-15","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/temporal-tables-for-postgresql-15\/","title":{"rendered":"Temporal tables for PostgreSQL 15?"},"content":{"rendered":"<p>One of the features which is currently missing in PostgreSQL is Temporal Tables. Other database systems have that since a long time and many people want to have in PostgreSQL as well. If you don&#8217;t know what it is, <a href=\"https:\/\/en.wikipedia.org\/wiki\/SQL:2011\" target=\"_blank\" rel=\"noopener\">here<\/a> is a short overview. Basically you can ask for a row as it was at a specific point in time. The <a href=\"https:\/\/www.postgresql.org\/message-id\/flat\/CALAY4q-cXCD0r4OybD%3Dw7Hr7F026ZUY6%3DLMsVPUe6yw_PJpTKQ%40mail.gmail.com\" target=\"_blank\" rel=\"noopener\">patch<\/a> which implements this, is currently in status <a href=\"https:\/\/commitfest.postgresql.org\/34\/2316\/\" target=\"_blank\" rel=\"noopener\">&#8220;Ready for Committer&#8221;<\/a>, which does not guarantee that it actually will be committed, but it should be in shape to test the functionality that comes with it.<\/p>\n<p><!--more--><\/p>\n<p>Having applied the patch and re-compiled PostgreSQL the feature can be tested. The most easy way to enable the feature for a table is this:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres=# create table t1 ( a int primary key generated always as identity\n                           , b text ) \n                           with system versioning;\nCREATE TABLE\npostgres=# d t1\n                                      Table \"public.t1\"\n  Column   |           Type           | Collation | Nullable |            Default            \n-----------+--------------------------+-----------+----------+-------------------------------\n a         | integer                  |           | not null | generated always as identity\n b         | text                     |           |          | \n starttime | timestamp with time zone |           | not null | generated always as row start\n endtime   | timestamp with time zone |           | not null | generated always as row end\nIndexes:\n    \"t1_pkey\" PRIMARY KEY, btree (a, endtime)\n<\/pre>\n<p>Two additional columns have been added automatically, starttime and endtime. If you don&#8217;t like the column names you can also do it like this:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres=# create table t2 ( a int primary key generated always as identity\n                           , b text\n                           , mystart timestamptz generated always as row start\n                           , myend timestamptz generated always as row end );\nCREATE TABLE\npostgres=# d t2\n                                     Table \"public.t2\"\n Column  |           Type           | Collation | Nullable |            Default            \n---------+--------------------------+-----------+----------+-------------------------------\n a       | integer                  |           | not null | generated always as identity\n b       | text                     |           |          | \n mystart | timestamp with time zone |           | not null | generated always as row start\n myend   | timestamp with time zone |           | not null | generated always as row end\nIndexes:\n    \"t2_pkey\" PRIMARY KEY, btree (a, myend)\n<\/pre>\n<p>Note that the two columns need to be defined as <a href=\"https:\/\/www.postgresql.org\/docs\/current\/datatype-datetime.html\" target=\"_blank\" rel=\"noopener\">timestamptz<\/a>:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres=# create table t3 ( a int primary key generated always as identity, b text, mystart date generated always as row start, myend date generated always as row end );\nERROR:  data type of row start time must be timestamptz\n<\/pre>\n<p>To see the feature in action lets add some data, fire an update and have a look at the history:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres=# insert into t1 (b) select md5(a::text) from generate_series(1,10) a;\nINSERT 0 10\npostgres=# update t1 set b = random()::text;\nUPDATE 10\npostgres=# select * from t1 for system_time from '-infinity' to 'infinity' order by a;\n a  |                b                 |           starttime           |            endtime            \n----+----------------------------------+-------------------------------+-------------------------------\n  1 | c4ca4238a0b923820dcc509a6f75849b | 2021-08-24 16:16:25.648976+02 | 2021-08-24 16:16:55.417076+02\n  1 | 0.42111793538855835              | 2021-08-24 16:16:55.417076+02 | infinity\n  2 | c81e728d9d4c2f636f067f89cc14862c | 2021-08-24 16:16:25.648976+02 | 2021-08-24 16:16:55.417076+02\n  2 | 0.5479128803753532               | 2021-08-24 16:16:55.417076+02 | infinity\n  3 | eccbc87e4b5ce2fe28308fd9f2a7baf3 | 2021-08-24 16:16:25.648976+02 | 2021-08-24 16:16:55.417076+02\n  3 | 0.5512468293024142               | 2021-08-24 16:16:55.417076+02 | infinity\n  4 | a87ff679a2f3e71d9181a67b7542122c | 2021-08-24 16:16:25.648976+02 | 2021-08-24 16:16:55.417076+02\n  4 | 0.4112741522472554               | 2021-08-24 16:16:55.417076+02 | infinity\n  5 | e4da3b7fbbce2345d7772b0674a318d5 | 2021-08-24 16:16:25.648976+02 | 2021-08-24 16:16:55.417076+02\n  5 | 0.46017420469036807              | 2021-08-24 16:16:55.417076+02 | infinity\n  6 | 1679091c5a880faf6fb5e6087eb1b2dc | 2021-08-24 16:16:25.648976+02 | 2021-08-24 16:16:55.417076+02\n  6 | 0.3495216613664702               | 2021-08-24 16:16:55.417076+02 | infinity\n  7 | 8f14e45fceea167a5a36dedd4bea2543 | 2021-08-24 16:16:25.648976+02 | 2021-08-24 16:16:55.417076+02\n  7 | 0.2657576876373895               | 2021-08-24 16:16:55.417076+02 | infinity\n  8 | c9f0f895fb98ab9159f51fd0297e236d | 2021-08-24 16:16:25.648976+02 | 2021-08-24 16:16:55.417076+02\n  8 | 0.9808748465536858               | 2021-08-24 16:16:55.417076+02 | infinity\n  9 | 45c48cce2e2d7fbdea1afc51c7c6ad26 | 2021-08-24 16:16:25.648976+02 | 2021-08-24 16:16:55.417076+02\n  9 | 0.4533070845652887               | 2021-08-24 16:16:55.417076+02 | infinity\n 10 | d3d9446802a44259755d38e6d163e820 | 2021-08-24 16:16:25.648976+02 | 2021-08-24 16:16:55.417076+02\n 10 | 0.20914767879762408              | 2021-08-24 16:16:55.417076+02 | infinity\n(20 rows)\n<\/pre>\n<p>The rows with &#8220;endtime = infinity&#8221; are the current ones, the ones with an actual endtime are historical versions. Doing the almost same thing again makes it more clear:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres=# update t1 set b = 'xxxxx';\nUPDATE 10\npostgres=# select * from t1 for system_time from '-infinity' to 'infinity' order by a, starttime;\n a  |                b                 |           starttime           |            endtime            \n----+----------------------------------+-------------------------------+-------------------------------\n  1 | c4ca4238a0b923820dcc509a6f75849b | 2021-08-24 16:16:25.648976+02 | 2021-08-24 16:16:55.417076+02\n  1 | 0.42111793538855835              | 2021-08-24 16:16:55.417076+02 | 2021-08-24 16:58:24.799322+02\n  1 | xxxxx                            | 2021-08-24 16:58:24.799322+02 | infinity\n  2 | c81e728d9d4c2f636f067f89cc14862c | 2021-08-24 16:16:25.648976+02 | 2021-08-24 16:16:55.417076+02\n  2 | 0.5479128803753532               | 2021-08-24 16:16:55.417076+02 | 2021-08-24 16:58:24.799322+02\n  2 | xxxxx                            | 2021-08-24 16:58:24.799322+02 | infinity\n  3 | eccbc87e4b5ce2fe28308fd9f2a7baf3 | 2021-08-24 16:16:25.648976+02 | 2021-08-24 16:16:55.417076+02\n  3 | 0.5512468293024142               | 2021-08-24 16:16:55.417076+02 | 2021-08-24 16:58:24.799322+02\n  3 | xxxxx                            | 2021-08-24 16:58:24.799322+02 | infinity\n  4 | a87ff679a2f3e71d9181a67b7542122c | 2021-08-24 16:16:25.648976+02 | 2021-08-24 16:16:55.417076+02\n  4 | 0.4112741522472554               | 2021-08-24 16:16:55.417076+02 | 2021-08-24 16:58:24.799322+02\n  4 | xxxxx                            | 2021-08-24 16:58:24.799322+02 | infinity\n  5 | e4da3b7fbbce2345d7772b0674a318d5 | 2021-08-24 16:16:25.648976+02 | 2021-08-24 16:16:55.417076+02\n  5 | 0.46017420469036807              | 2021-08-24 16:16:55.417076+02 | 2021-08-24 16:58:24.799322+02\n  5 | xxxxx                            | 2021-08-24 16:58:24.799322+02 | infinity\n  6 | 1679091c5a880faf6fb5e6087eb1b2dc | 2021-08-24 16:16:25.648976+02 | 2021-08-24 16:16:55.417076+02\n  6 | 0.3495216613664702               | 2021-08-24 16:16:55.417076+02 | 2021-08-24 16:58:24.799322+02\n  6 | xxxxx                            | 2021-08-24 16:58:24.799322+02 | infinity\n  7 | 8f14e45fceea167a5a36dedd4bea2543 | 2021-08-24 16:16:25.648976+02 | 2021-08-24 16:16:55.417076+02\n  7 | 0.2657576876373895               | 2021-08-24 16:16:55.417076+02 | 2021-08-24 16:58:24.799322+02\n  7 | xxxxx                            | 2021-08-24 16:58:24.799322+02 | infinity\n  8 | c9f0f895fb98ab9159f51fd0297e236d | 2021-08-24 16:16:25.648976+02 | 2021-08-24 16:16:55.417076+02\n  8 | 0.9808748465536858               | 2021-08-24 16:16:55.417076+02 | 2021-08-24 16:58:24.799322+02\n  8 | xxxxx                            | 2021-08-24 16:58:24.799322+02 | infinity\n  9 | 45c48cce2e2d7fbdea1afc51c7c6ad26 | 2021-08-24 16:16:25.648976+02 | 2021-08-24 16:16:55.417076+02\n  9 | 0.4533070845652887               | 2021-08-24 16:16:55.417076+02 | 2021-08-24 16:58:24.799322+02\n  9 | xxxxx                            | 2021-08-24 16:58:24.799322+02 | infinity\n 10 | d3d9446802a44259755d38e6d163e820 | 2021-08-24 16:16:25.648976+02 | 2021-08-24 16:16:55.417076+02\n 10 | 0.20914767879762408              | 2021-08-24 16:16:55.417076+02 | 2021-08-24 16:58:24.799322+02\n 10 | xxxxx                            | 2021-08-24 16:58:24.799322+02 | infinity\n(30 rows)\n<\/pre>\n<p>Without specifying any time frame you get the current version of rows, of course:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres=# select * from t1 order by a;\n a  |   b   |           starttime           | endtime  \n----+-------+-------------------------------+----------\n  1 | xxxxx | 2021-08-24 16:58:24.799322+02 | infinity\n  2 | xxxxx | 2021-08-24 16:58:24.799322+02 | infinity\n  3 | xxxxx | 2021-08-24 16:58:24.799322+02 | infinity\n  4 | xxxxx | 2021-08-24 16:58:24.799322+02 | infinity\n  5 | xxxxx | 2021-08-24 16:58:24.799322+02 | infinity\n  6 | xxxxx | 2021-08-24 16:58:24.799322+02 | infinity\n  7 | xxxxx | 2021-08-24 16:58:24.799322+02 | infinity\n  8 | xxxxx | 2021-08-24 16:58:24.799322+02 | infinity\n  9 | xxxxx | 2021-08-24 16:58:24.799322+02 | infinity\n 10 | xxxxx | 2021-08-24 16:58:24.799322+02 | infinity\n<\/pre>\n<p>Asking for a specific point in time works as well:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres=# select * from t1 for system_time as of '2021-08-24 16:16:25.648976+02'::timestamptz;\n a  |                b                 |           starttime           |            endtime            \n----+----------------------------------+-------------------------------+-------------------------------\n  1 | c4ca4238a0b923820dcc509a6f75849b | 2021-08-24 16:16:25.648976+02 | 2021-08-24 16:16:55.417076+02\n  2 | c81e728d9d4c2f636f067f89cc14862c | 2021-08-24 16:16:25.648976+02 | 2021-08-24 16:16:55.417076+02\n  3 | eccbc87e4b5ce2fe28308fd9f2a7baf3 | 2021-08-24 16:16:25.648976+02 | 2021-08-24 16:16:55.417076+02\n  4 | a87ff679a2f3e71d9181a67b7542122c | 2021-08-24 16:16:25.648976+02 | 2021-08-24 16:16:55.417076+02\n  5 | e4da3b7fbbce2345d7772b0674a318d5 | 2021-08-24 16:16:25.648976+02 | 2021-08-24 16:16:55.417076+02\n  6 | 1679091c5a880faf6fb5e6087eb1b2dc | 2021-08-24 16:16:25.648976+02 | 2021-08-24 16:16:55.417076+02\n  7 | 8f14e45fceea167a5a36dedd4bea2543 | 2021-08-24 16:16:25.648976+02 | 2021-08-24 16:16:55.417076+02\n  8 | c9f0f895fb98ab9159f51fd0297e236d | 2021-08-24 16:16:25.648976+02 | 2021-08-24 16:16:55.417076+02\n  9 | 45c48cce2e2d7fbdea1afc51c7c6ad26 | 2021-08-24 16:16:25.648976+02 | 2021-08-24 16:16:55.417076+02\n 10 | d3d9446802a44259755d38e6d163e820 | 2021-08-24 16:16:25.648976+02 | 2021-08-24 16:16:55.417076+02\n<\/pre>\n<p>&#8230; or asking like this:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres=# select * from t1 for system_time between '2021-08-24 16:16:55.417076+02'::timestamptz and '2021-08-24 16:58:24.799322+02'::timestamptz;\n a  |          b          |           starttime           |            endtime            \n----+---------------------+-------------------------------+-------------------------------\n  1 | 0.42111793538855835 | 2021-08-24 16:16:55.417076+02 | 2021-08-24 16:58:24.799322+02\n  1 | xxxxx               | 2021-08-24 16:58:24.799322+02 | infinity\n  2 | 0.5479128803753532  | 2021-08-24 16:16:55.417076+02 | 2021-08-24 16:58:24.799322+02\n  2 | xxxxx               | 2021-08-24 16:58:24.799322+02 | infinity\n  3 | 0.5512468293024142  | 2021-08-24 16:16:55.417076+02 | 2021-08-24 16:58:24.799322+02\n  3 | xxxxx               | 2021-08-24 16:58:24.799322+02 | infinity\n  4 | 0.4112741522472554  | 2021-08-24 16:16:55.417076+02 | 2021-08-24 16:58:24.799322+02\n  4 | xxxxx               | 2021-08-24 16:58:24.799322+02 | infinity\n  5 | 0.46017420469036807 | 2021-08-24 16:16:55.417076+02 | 2021-08-24 16:58:24.799322+02\n  5 | xxxxx               | 2021-08-24 16:58:24.799322+02 | infinity\n  6 | 0.3495216613664702  | 2021-08-24 16:16:55.417076+02 | 2021-08-24 16:58:24.799322+02\n  6 | xxxxx               | 2021-08-24 16:58:24.799322+02 | infinity\n  7 | 0.2657576876373895  | 2021-08-24 16:16:55.417076+02 | 2021-08-24 16:58:24.799322+02\n  7 | xxxxx               | 2021-08-24 16:58:24.799322+02 | infinity\n  8 | 0.9808748465536858  | 2021-08-24 16:16:55.417076+02 | 2021-08-24 16:58:24.799322+02\n  8 | xxxxx               | 2021-08-24 16:58:24.799322+02 | infinity\n  9 | 0.4533070845652887  | 2021-08-24 16:16:55.417076+02 | 2021-08-24 16:58:24.799322+02\n  9 | xxxxx               | 2021-08-24 16:58:24.799322+02 | infinity\n 10 | 0.20914767879762408 | 2021-08-24 16:16:55.417076+02 | 2021-08-24 16:58:24.799322+02\n 10 | xxxxx               | 2021-08-24 16:58:24.799322+02 | infinity\n<\/pre>\n<p>Really looks promising. Thanks to all involved.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>One of the features which is currently missing in PostgreSQL is Temporal Tables. Other database systems have that since a long time and many people want to have in PostgreSQL as well. If you don&#8217;t know what it is, here is a short overview. Basically you can ask for a row as it was at [&hellip;]<\/p>\n","protected":false},"author":29,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229],"tags":[77],"type_dbi":[],"class_list":["post-16671","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","tag-postgresql"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v27.2 (Yoast SEO v27.4) - https:\/\/yoast.com\/product\/yoast-seo-premium-wordpress\/ -->\n<title>Temporal tables for PostgreSQL 15? - dbi Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/temporal-tables-for-postgresql-15\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Temporal tables for PostgreSQL 15?\" \/>\n<meta property=\"og:description\" content=\"One of the features which is currently missing in PostgreSQL is Temporal Tables. Other database systems have that since a long time and many people want to have in PostgreSQL as well. If you don&#8217;t know what it is, here is a short overview. Basically you can ask for a row as it was at [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/temporal-tables-for-postgresql-15\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2021-08-26T12:00:38+00:00\" \/>\n<meta name=\"author\" content=\"Daniel Westermann\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@westermanndanie\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Daniel Westermann\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"6 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/temporal-tables-for-postgresql-15\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/temporal-tables-for-postgresql-15\\\/\"},\"author\":{\"name\":\"Daniel Westermann\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"headline\":\"Temporal tables for PostgreSQL 15?\",\"datePublished\":\"2021-08-26T12:00:38+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/temporal-tables-for-postgresql-15\\\/\"},\"wordCount\":245,\"commentCount\":0,\"keywords\":[\"PostgreSQL\"],\"articleSection\":[\"Database Administration &amp; Monitoring\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/temporal-tables-for-postgresql-15\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/temporal-tables-for-postgresql-15\\\/\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/temporal-tables-for-postgresql-15\\\/\",\"name\":\"Temporal tables for PostgreSQL 15? - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\"},\"datePublished\":\"2021-08-26T12:00:38+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/temporal-tables-for-postgresql-15\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/temporal-tables-for-postgresql-15\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/temporal-tables-for-postgresql-15\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Temporal tables for PostgreSQL 15?\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/8d08e9bd996a89bd75c0286cbabf3c66\",\"name\":\"Daniel Westermann\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"caption\":\"Daniel Westermann\"},\"description\":\"Daniel Westermann is Principal Consultant and Technology Leader Open Infrastructure at dbi services. He has more than 15 years of experience in management, engineering and optimization of databases and infrastructures, especially on Oracle and PostgreSQL. Since the beginning of his career, he has specialized in Oracle Technologies and is Oracle Certified Professional 12c and Oracle Certified Expert RAC\\\/GridInfra. Over time, Daniel has become increasingly interested in open source technologies, becoming \u201cTechnology Leader Open Infrastructure\u201d and PostgreSQL expert. \u00a0Based on community or EnterpriseDB tools, he develops and installs complex high available solutions with PostgreSQL. He is also a certified PostgreSQL Plus 9.0 Professional and a Postgres Advanced Server 9.4 Professional. He is a regular speaker at PostgreSQL conferences in Switzerland and Europe. Today Daniel is also supporting our customers on AWS services such as AWS RDS, database migrations into the cloud, EC2 and automated infrastructure management with AWS SSM (System Manager). He is a certified AWS Solutions Architect Professional. Prior to dbi services, Daniel was Management System Engineer at LC SYSTEMS-Engineering AG in Basel. Before that, he worked as Oracle Developper &amp;\u00a0Project Manager at Delta Energy Solutions AG in Basel (today Powel AG). Daniel holds a diploma in Business Informatics (DHBW, Germany). His branch-related experience mainly covers the pharma industry, the financial sector, energy, lottery and telecommunications.\",\"sameAs\":[\"https:\\\/\\\/x.com\\\/westermanndanie\"],\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/author\\\/daniel-westermann\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Temporal tables for PostgreSQL 15? - dbi Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/temporal-tables-for-postgresql-15\/","og_locale":"en_US","og_type":"article","og_title":"Temporal tables for PostgreSQL 15?","og_description":"One of the features which is currently missing in PostgreSQL is Temporal Tables. Other database systems have that since a long time and many people want to have in PostgreSQL as well. If you don&#8217;t know what it is, here is a short overview. Basically you can ask for a row as it was at [&hellip;]","og_url":"https:\/\/www.dbi-services.com\/blog\/temporal-tables-for-postgresql-15\/","og_site_name":"dbi Blog","article_published_time":"2021-08-26T12:00:38+00:00","author":"Daniel Westermann","twitter_card":"summary_large_image","twitter_creator":"@westermanndanie","twitter_misc":{"Written by":"Daniel Westermann","Est. reading time":"6 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/temporal-tables-for-postgresql-15\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/temporal-tables-for-postgresql-15\/"},"author":{"name":"Daniel Westermann","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"headline":"Temporal tables for PostgreSQL 15?","datePublished":"2021-08-26T12:00:38+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/temporal-tables-for-postgresql-15\/"},"wordCount":245,"commentCount":0,"keywords":["PostgreSQL"],"articleSection":["Database Administration &amp; Monitoring"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/temporal-tables-for-postgresql-15\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/temporal-tables-for-postgresql-15\/","url":"https:\/\/www.dbi-services.com\/blog\/temporal-tables-for-postgresql-15\/","name":"Temporal tables for PostgreSQL 15? - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2021-08-26T12:00:38+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/temporal-tables-for-postgresql-15\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/temporal-tables-for-postgresql-15\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/temporal-tables-for-postgresql-15\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Temporal tables for PostgreSQL 15?"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66","name":"Daniel Westermann","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","caption":"Daniel Westermann"},"description":"Daniel Westermann is Principal Consultant and Technology Leader Open Infrastructure at dbi services. He has more than 15 years of experience in management, engineering and optimization of databases and infrastructures, especially on Oracle and PostgreSQL. Since the beginning of his career, he has specialized in Oracle Technologies and is Oracle Certified Professional 12c and Oracle Certified Expert RAC\/GridInfra. Over time, Daniel has become increasingly interested in open source technologies, becoming \u201cTechnology Leader Open Infrastructure\u201d and PostgreSQL expert. \u00a0Based on community or EnterpriseDB tools, he develops and installs complex high available solutions with PostgreSQL. He is also a certified PostgreSQL Plus 9.0 Professional and a Postgres Advanced Server 9.4 Professional. He is a regular speaker at PostgreSQL conferences in Switzerland and Europe. Today Daniel is also supporting our customers on AWS services such as AWS RDS, database migrations into the cloud, EC2 and automated infrastructure management with AWS SSM (System Manager). He is a certified AWS Solutions Architect Professional. Prior to dbi services, Daniel was Management System Engineer at LC SYSTEMS-Engineering AG in Basel. Before that, he worked as Oracle Developper &amp;\u00a0Project Manager at Delta Energy Solutions AG in Basel (today Powel AG). Daniel holds a diploma in Business Informatics (DHBW, Germany). His branch-related experience mainly covers the pharma industry, the financial sector, energy, lottery and telecommunications.","sameAs":["https:\/\/x.com\/westermanndanie"],"url":"https:\/\/www.dbi-services.com\/blog\/author\/daniel-westermann\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/16671","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/29"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=16671"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/16671\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=16671"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=16671"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=16671"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=16671"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}