On 1st of April 2026, Peter Eisentraut committed patch:
Add UPDATE/DELETE FOR PORTION OF This is an extension of the UPDATE and DELETE commands to do a "temporal update/delete" based on a range or multirange column. The user can say UPDATE t FOR PORTION OF valid_at FROM '2001-01-01' TO '2002-01-01' SET ... (or likewise with DELETE) where valid_at is a range or multirange column. The command is automatically limited to rows overlapping the targeted portion, and only history within those bounds is changed. If a row represents history partly inside and partly outside the bounds, then the command truncates the row's application time to fit within the targeted portion, then it inserts one or more "temporal leftovers": new rows containing all the original values, except with the application-time column changed to only represent the untouched part of history. To compute the temporal leftovers that are required, we use the *_minus_multi set-returning functions defined in 5eed8ce50c. - Added bison support for FOR PORTION OF syntax. The bounds must be constant, so we forbid column references, subqueries, etc. We do accept functions like NOW(). - Added logic to executor to insert new rows for the "temporal leftover" part of a record touched by a FOR PORTION OF query. - Documented FOR PORTION OF. - Added tests. Author: Paul A. Jungwirth <pj@illuminatedcomputing.com> Reviewed-by: Peter Eisentraut <peter@eisentraut.org> Discussion: https://www.postgresql.org/message-id/flat/ec498c3d-5f2b-48ec-b989-5561c8aa2024%40illuminatedcomputing.com
In PostgreSQL 18 we got temporal tables. Basically a way to have rows track their history in time, and a way to query state at specific point in time.
This new commit adds significant simplification to how we can orchestrate updates and deletes.
Let's see what I had to do before, first some sample data:
=$ CREATE extension btree_gist; CREATE =$ create table test_table ( id int8 generated by default as identity, valid_range tstzrange not null default tstzrange(now(), 'infinity', '[)'), the_value TEXT, primary key (id, valid_range WITHOUT OVERLAPS) ); CREATE TABLE =$ INSERT INTO test_table (valid_range, the_value) VALUES (tstzrange(now() - '1 year'::INTERVAL, 'infinity', '[)'), 'initial'); INSERT 0 1 =$ INSERT INTO test_table (valid_range, the_value) VALUES (tstzrange(now() - '1 year'::INTERVAL, 'infinity', '[)'), 'second initial'); INSERT 0 1 =$ SELECT * FROM test_table; id | valid_range | the_value ----+--------------------------------------------+---------------- 1 | ["2025-04-02 12:29:42.375018+02",infinity) | initial 2 | ["2025-04-02 12:29:42.378174+02",infinity) | second initial (2 rows)
Now, let's change value for row with id = 1. I have first to mark previous version as invalid, and then insert new one, and do it all in transaction, to make sure that all is consistent:
=$ BEGIN; BEGIN =$ UPDATE test_table SET valid_range = tstzrange( lower( valid_range ), now(), '[)') WHERE id = 1 AND valid_range @> now(); UPDATE 1 =$ INSERT INTO test_table (id, the_value) VALUES (1, 'updated'); INSERT 0 1 =$ commit; COMMIT
Now, the table contains three rows:
=$ SELECT * FROM test_table; id | valid_range | the_value ----+-------------------------------------------------------------------+---------------- 2 | ["2025-04-02 12:29:42.378174+02",infinity) | second initial 1 | ["2025-04-02 12:29:42.375018+02","2026-04-02 12:29:42.380359+02") | initial 1 | ["2026-04-02 12:29:42.380359+02",infinity) | updated (3 rows)
but, of course we can query just the ones visible now:
=$ SELECT * FROM test_table WHERE valid_range @> now(); id | valid_range | the_value ----+--------------------------------------------+---------------- 2 | ["2025-04-02 12:29:42.378174+02",infinity) | second initial 1 | ["2026-04-02 12:29:42.380359+02",infinity) | updated (2 rows)
Deleting of row was simpler, I just had to update current version of row:
=$ UPDATE test_table SET valid_range = tstzrange( lower( valid_range ), now(), '[)') WHERE id = 1 AND valid_range @> now();; UPDATE 1 =$ SELECT * FROM test_table; id | valid_range | the_value ----+-------------------------------------------------------------------+---------------- 2 | ["2025-04-02 12:29:42.378174+02",infinity) | second initial 1 | ["2025-04-02 12:29:42.375018+02","2026-04-02 12:29:42.380359+02") | initial 1 | ["2026-04-02 12:29:42.380359+02","2026-04-02 12:29:42.382341+02") | updated (3 rows) =$ SELECT * FROM test_table WHERE valid_range @> now(); id | valid_range | the_value ----+--------------------------------------------+---------------- 2 | ["2025-04-02 12:29:42.378174+02",infinity) | second initial (1 row)
This is how it was in Pg18. But now, I can simply:
=$ update test_table for portion of valid_range from now() to 'infinity' set the_value = 'new value' where id = 2; UPDATE 1 =$ select * from test_table; id │ valid_range │ the_value ────┼───────────────────────────────────────────────────────────────────┼──────────────── 1 │ ["2025-04-02 12:29:42.375018+02","2026-04-02 12:29:42.380359+02") │ initial 1 │ ["2026-04-02 12:29:42.380359+02","2026-04-02 12:29:42.382341+02") │ updated 2 │ ["2026-04-02 12:33:39.740173+02",infinity) │ new value 2 │ ["2025-04-02 12:29:42.378174+02","2026-04-02 12:33:39.740173+02") │ second initial (4 rows) =$ update test_table for portion of valid_range from now() to 'infinity' set the_value = 'yet another value' where id = 2; UPDATE 1 =$ select * from test_table; id │ valid_range │ the_value ────┼───────────────────────────────────────────────────────────────────┼─────────────────── 1 │ ["2025-04-02 12:29:42.375018+02","2026-04-02 12:29:42.380359+02") │ initial 1 │ ["2026-04-02 12:29:42.380359+02","2026-04-02 12:29:42.382341+02") │ updated 2 │ ["2025-04-02 12:29:42.378174+02","2026-04-02 12:33:39.740173+02") │ second initial 2 │ ["2026-04-02 12:33:51.701421+02",infinity) │ yet another value 2 │ ["2026-04-02 12:33:39.740173+02","2026-04-02 12:33:51.701421+02") │ new value (5 rows)
What's cool, is that I can also easily change the data in the past. For example:
=$ update test_table for portion of valid_range from '2025-12-01' to '2026-01-01' set the_value = 'december thing' where id = 2; UPDATE 1 =$ select * from test_table order by id, valid_range; id │ valid_range │ the_value ────┼───────────────────────────────────────────────────────────────────┼─────────────────── 1 │ ["2025-04-02 12:29:42.375018+02","2026-04-02 12:29:42.380359+02") │ initial 1 │ ["2026-04-02 12:29:42.380359+02","2026-04-02 12:29:42.382341+02") │ updated 2 │ ["2025-04-02 12:29:42.378174+02","2025-12-01 00:00:00+01") │ second initial 2 │ ["2025-12-01 00:00:00+01","2026-01-01 00:00:00+01") │ december thing 2 │ ["2026-01-01 00:00:00+01","2026-04-02 12:33:39.740173+02") │ second initial 2 │ ["2026-04-02 12:33:39.740173+02","2026-04-02 12:33:51.701421+02") │ new value 2 │ ["2026-04-02 12:33:51.701421+02",infinity) │ yet another value (7 rows)
Similarly I can delete data from:
=$ delete from test_table for portion of valid_range from now() to 'infinity' where id = 2; DELETE 1 =$ select * from test_table order by id, valid_range; id │ valid_range │ the_value ────┼───────────────────────────────────────────────────────────────────┼─────────────────── 1 │ ["2025-04-02 12:29:42.375018+02","2026-04-02 12:29:42.380359+02") │ initial 1 │ ["2026-04-02 12:29:42.380359+02","2026-04-02 12:29:42.382341+02") │ updated 2 │ ["2025-04-02 12:29:42.378174+02","2025-12-01 00:00:00+01") │ second initial 2 │ ["2025-12-01 00:00:00+01","2026-01-01 00:00:00+01") │ december thing 2 │ ["2026-01-01 00:00:00+01","2026-04-02 12:33:39.740173+02") │ second initial 2 │ ["2026-04-02 12:33:39.740173+02","2026-04-02 12:33:51.701421+02") │ new value 2 │ ["2026-04-02 12:33:51.701421+02","2026-04-02 12:42:24.691632+02") │ yet another value (7 rows)
and, of course I can delete the row from subset of history:
=$ delete from test_table for portion of valid_range from '2025-10-01' to '2025-11-01' where id = 2; DELETE 1 =$ select * from test_table where id = 2 order by valid_range; id │ valid_range │ the_value ────┼───────────────────────────────────────────────────────────────────┼─────────────────── 2 │ ["2025-04-02 12:29:42.378174+02","2025-10-01 00:00:00+02") │ second initial 2 │ ["2025-11-01 00:00:00+01","2025-12-01 00:00:00+01") │ second initial 2 │ ["2025-12-01 00:00:00+01","2026-01-01 00:00:00+01") │ december thing 2 │ ["2026-01-01 00:00:00+01","2026-04-02 12:33:39.740173+02") │ second initial 2 │ ["2026-04-02 12:33:39.740173+02","2026-04-02 12:33:51.701421+02") │ new value 2 │ ["2026-04-02 12:33:51.701421+02","2026-04-02 12:42:24.691632+02") │ yet another value (6 rows)
This might not be clear, so let's see the state of record with id = 2 at various point in time:
=$ select p, d.* from generate_series( '2025-04-01'::date, '2026-05-01'::date, '1 month'::interval) p left join lateral (select * from test_table where id = 2 and valid_range @> p ) d on (true); p │ id │ valid_range │ the_value ────────────────────────┼────────┼────────────────────────────────────────────────────────────┼──────────────── 2025-04-01 00:00:00+02 │ [null] │ [null] │ [null] 2025-05-01 00:00:00+02 │ 2 │ ["2025-04-02 12:29:42.378174+02","2025-10-01 00:00:00+02") │ second initial 2025-06-01 00:00:00+02 │ 2 │ ["2025-04-02 12:29:42.378174+02","2025-10-01 00:00:00+02") │ second initial 2025-07-01 00:00:00+02 │ 2 │ ["2025-04-02 12:29:42.378174+02","2025-10-01 00:00:00+02") │ second initial 2025-08-01 00:00:00+02 │ 2 │ ["2025-04-02 12:29:42.378174+02","2025-10-01 00:00:00+02") │ second initial 2025-09-01 00:00:00+02 │ 2 │ ["2025-04-02 12:29:42.378174+02","2025-10-01 00:00:00+02") │ second initial 2025-10-01 00:00:00+02 │ [null] │ [null] │ [null] 2025-11-01 00:00:00+01 │ 2 │ ["2025-11-01 00:00:00+01","2025-12-01 00:00:00+01") │ second initial 2025-12-01 00:00:00+01 │ 2 │ ["2025-12-01 00:00:00+01","2026-01-01 00:00:00+01") │ december thing 2026-01-01 00:00:00+01 │ 2 │ ["2026-01-01 00:00:00+01","2026-04-02 12:33:39.740173+02") │ second initial 2026-02-01 00:00:00+01 │ 2 │ ["2026-01-01 00:00:00+01","2026-04-02 12:33:39.740173+02") │ second initial 2026-03-01 00:00:00+01 │ 2 │ ["2026-01-01 00:00:00+01","2026-04-02 12:33:39.740173+02") │ second initial 2026-04-01 00:00:00+02 │ 2 │ ["2026-01-01 00:00:00+01","2026-04-02 12:33:39.740173+02") │ second initial 2026-05-01 00:00:00+02 │ [null] │ [null] │ [null] (14 rows)
Where NULL value in id column simple means that at the time there was no valid row with id = 2.
Pretty awesome. Thanks a lot to everyone involved.