The PostgreSQL 19 beta release includes a new command that adds temporal constraint support that we had in version 18. In this blog, we will dive deep into how this new command works and what things you should consider when using this command.
Why Do We Need This Feature?
In some applications, data will not be static; that is, it may vary based on the timeline. For example, suppose you have a table to keep the price of products, but the product's price may change by season, so you need to manage those changes for specific date ranges; then this feature would be helpful for you.
Before we had this feature, if a product's price was $20 for the entire year 2026 and you wanted to raise the price to $25 only for the summer months (June through August), a standard UPDATE could not handle it.
You might have to code your application or use PL/pgSQL triggers to do a sequence of operations:
- Detect the overlap.
- Shrink the original row to end in June (the past leftover).
- Insert a new row for months from June through August with a price of $25.
- Then add another new row from September with the real price of $20 and for the other future months.
The PostgreSQL 19 Beta version solved this problem by having all these steps to be done by a single command FOR PORTION OF, so that PostgreSQL itself handles overlap detection, does the row splitting, and manages the leftover rows. This will help us to maintain the temporal data without having complex application code and avoid possible bugs.
Considerations and Table Creation
Before using the FOR PORTION OF commands, you must ensure that the database is set up correctly :
- The Concurrency Issue: PostgreSQL defaults to the READ COMMITTED isolation level; there might occur issues with concurrent transactions while using FOR PORTION OF since there are possibilities that the concurrent transactions might step on each other. This issue can be avoided by running SELECT ... FOR UPDATE right before your FOR PORTION OF query, you force concurrent transactions to wait and rescan the table to see if any new rows are added.
- Data Types: You cannot use separate start_date and end_date columns for these specific SQL commands. You must use Postgres's native range types (like daterange or tstzrange).
- Requires GIST Index: To avoid overlapping of timelines, you must use the WITHOUT OVERLAPS clause for the Primary Key of the table; for that, we need to use btree_gist extension, in order to get scalar IDs against range types.
How to Create the Tables
Let’s go through an example to see how we need to set up the temporal table to make this feature work.
1. Enable the btree_gist extension for temporal constraints:
CREATE EXTENSION IF NOT EXISTS btree_gist;
2. Create the table by using DATERANGE type:
CREATE TABLE product_product ( product_id INT NOT NULL, unit_price NUMERIC(10,2) NOT NULL, valid_at DATERANGE NOT NULL,
-- Enforce temporal integrity (no overlapping dates for the same product) PRIMARY KEY (product_id, valid_at WITHOUT OVERLAPS) );
3. Now let’s insert a baseline record valid for the whole year:
INSERT INTO product_product (product_id, unit_price, valid_at) VALUES (101, 20.00, daterange('2026-01-01', '2027-01-01', '[)'));How UPDATE Works
For UPDATEs we will use UPDATE ... FOR PORTION OF, in this way, you are telling Postgres to change data only within a specific time range.
How this works internally:
1. PostgreSQL finds the row/rows that meet the time range you specified.
2. It modifies the values and makes the row's valid_at range to fit within the range we had specified.
3. Postgres automatically calculates and inserts "temporal leftovers" if the original row went beyond your chosen section (into the past or the future). These are new rows that maintain the unchanged history by holding the previous values.
UPDATE product_product FOR PORTION OF valid_at FROM '2026-06-01' TO '2026-09-01' SET unit_price = 25.00 WHERE product_id = 101;
The Result: Your single original row is automatically split into three:
- [2026-01-01, 2026-06-01) at $20.00 (Leftover 1: Unmodified Spring)
- [2026-06-01, 2026-09-01) at $25.00 (The Targeted Update)
- [2026-09-01, 2027-01-01) at $20.00 (Leftover 2: Unmodified Fall/Winter)
How DELETE Works
A normal DELETE wipes out a row entirely. A temporal DELETE ... FOR PORTION OF deletes data out of a specific segment of a timeline, leaving the rest unchanged.
How it works internally:
- Postgres targets rows that overlap with the deletion window.
- It deletes the original overlapping rows entirely.
- Just like the update command, it calculates the historical "leftovers" that fell outside your deletion range and immediately inserts them back into the table.
The Query:
Imagine a supply chain failure means the product cannot be sold during July. You need to remove July from the database.
-- Delete the month of July from the timeline
DELETE FROM product_product
FOR PORTION OF valid_at FROM '2026-07-01' TO '2026-08-01'
WHERE product_id = 101;
The result would be like:
Postgres targets the summer row we created in Section 3 and punches a hole right through the middle of it. You are left with four total rows:
- [2026-01-01, 2026-06-01) at $20.00 (Spring)
- [2026-06-01, 2026-07-01) at $25.00 (June)
- (July is completely gone)
- [2026-08-01, 2026-09-01) at $25.00 (August)
- [2026-09-01, 2027-01-01) at $20.00 (Fall/Winter)
The Issue to be Noted while using Temporal changes
If you are a database developer, you might be panicking right now. You are probably thinking, "If my product splits into multiple rows, won't I get a Duplicate Primary Key error because the ID is no longer unique?"
In a normal database table, yes. The rule is strictly: An ID can only exist once. But for this to work, PostgreSQL 19 (and the SQL:2011 standard) requires you to use a Composite Temporal Primary Key. (used incase of product_product table)
This changes the golden rule to: An ID can exist multiple times, as long as the dates never overlap. When you create your table, you set up the Primary Key with a special `WITHOUT OVERLAPS` instruction:`PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)`
Imagine you have Product B, which costs $24 from July through December.
- The "Before" State (One Row):id: 2 | price: $24 | [July 1, Dec 31]
Now, you decide to raise the price to $28, but only from July to September. You run your `FOR PORTION OF` update, and Postgres splits the row.
- The "After" State (Two Rows): Row 1: id: 2 | price: $28 | [July 1, Sept 30] (Your updated summer price)
Row 2: id: 2 | price: $24 | [Oct 1, Dec 31] (The future leftover)
How the Database Survives
When Postgres goes to save that newly created "leftover" row, it checks the Temporal Primary Key constraint:
- Does ID 2 already exist? Yes.
- Do their time ranges overlap? Let's check:
- Row 1 ends on Sept 30.
- Row 2 begins on Oct 1 ie, they touch, but they do not overlap.
Because the time ranges do not overlap, the database accepts the split perfectly. The `id` is no longer the only thing that makes the row unique—it is the combination of the ID plus that specific slice of time.
Handling temporal data used to require a headache-inducing web of custom code, triggers, and manual row-splitting. With PostgreSQL 19’s new `FOR PORTION OF` syntax, the database engine finally takes over the hard work.