On 17th of September 2024, Peter Eisentraut committed patch:
Add temporal FOREIGN KEY contraints
Add PERIOD clause to foreign key constraint definitions. This is
supported for range and multirange types. Temporal foreign keys check
for range containment instead of equality.
This feature matches the behavior of the SQL standard temporal foreign
keys, but it works on PostgreSQL's native ranges instead of SQL's
"periods", which don't exist in PostgreSQL (yet).
Reference actions ON {UPDATE,DELETE} {CASCADE,SET NULL,SET DEFAULT}
are not supported yet.
(previously committed as 34768ee3616, reverted by 8aee330af55; this is
essentially unchanged from those)
Author: Paul A. Jungwirth <pj@illuminatedcomputing.com>
Reviewed-by: Peter Eisentraut <peter@eisentraut.org>
Reviewed-by: jian he <jian.universality@gmail.com>
Discussion: https://www.postgresql.org/message-id/flat/CA+renyUApHgSZF9-nd-a0+OPGharLQLO=mDHcY4_qQ0+noCUVg@mail.gmail.com
So, this is followup to recently posted adding temporal primary keys (and unique).
Thanks to info from then we now can make tables like:
=$ CREATE TABLE addresses ( id int8 generated BY DEFAULT AS IDENTITY, valid_range tstzrange NOT NULL DEFAULT tstzrange(now(), 'infinity', '[)'), recipient text NOT NULL, PRIMARY KEY (id, valid_range WITHOUT OVERLAPS) );
And now, thanks to this patch, we can have table that has foreign key to such addresses:
=$ CREATE TABLE orders ( id int8 generated BY DEFAULT AS IDENTITY, address_id int8 NOT NULL, address_valid_at tstzrange NOT NULL, content text, CONSTRAINT order_address FOREIGN KEY ( address_id, PERIOD address_valid_at ) REFERENCES addresses ( id, PERIOD valid_range ) );
Word PERIOD is keyword required by Pg (most likely due to SQL standard).
Given this situation, let's see how that would work. For address_valid_at I would pick singular moment in time – basically the moment when order was placed.
So, let's add some addresses:
=$ INSERT INTO addresses (id, valid_range, recipient) VALUES ( 1, '[2023-01-01,2024-01-01)', 'Address from 2023' ); INSERT 0 1 =$ INSERT INTO addresses (id, valid_range, recipient) VALUES ( 1, '[2024-01-01,2025-01-01)', 'Address from 2024' ); INSERT 0 1
And now orders. First I'll try to add order that shouldn't work – for invalid address:
=$ INSERT INTO orders (address_id, address_valid_at, content) VALUES (1, '[2022-06-15,2022-06-15]', 'Invalid address #1'); ERROR: INSERT OR UPDATE ON TABLE "orders" violates FOREIGN KEY CONSTRAINT "order_address" DETAIL: KEY (address_id, address_valid_at)=(1, ["2022-06-15 00:00:00+02","2022-06-15 00:00:00+02"]) IS NOT present IN TABLE "addresses". =$ INSERT INTO orders (address_id, address_valid_at, content) VALUES (2, '[2024-06-15,2024-06-15]', 'Invalid address #2'); ERROR: INSERT OR UPDATE ON TABLE "orders" violates FOREIGN KEY CONSTRAINT "order_address" DETAIL: KEY (address_id, address_valid_at)=(2, ["2024-06-15 00:00:00+02","2024-06-15 00:00:00+02"]) IS NOT present IN TABLE "addresses".
So far so good. So let's insert two orders that will work for different addresses:
=$ INSERT INTO orders (address_id, address_valid_at, content) VALUES (1, '[2023-06-15,2023-06-15]', 'Order with address from 2023'); INSERT 0 1 =$ INSERT INTO orders (address_id, address_valid_at, content) VALUES (1, '[2024-06-15,2024-06-15]', 'Order with address from 2024'); INSERT 0 1
Works. Now, the question is – how do I query the data to join the tables?
=$ SELECT o.id, o.content, a.id, a.recipient FROM orders o JOIN addresses a ON o.address_id = a.id AND a.valid_range @> o.address_valid_at ; id | content | id | recipient ----+------------------------------+----+------------------- 3 | ORDER WITH address FROM 2023 | 1 | Address FROM 2023 4 | ORDER WITH address FROM 2024 | 1 | Address FROM 2024 (2 ROWS)
All simple, clear (well, if you know range operators) and functional.
Just for completeness, what will happen if I'd try to delete an address that was used in some order?
=$ DELETE FROM addresses WHERE recipient = 'Address from 2023'; ERROR: UPDATE OR DELETE ON TABLE "addresses" violates FOREIGN KEY CONSTRAINT "order_address" ON TABLE "orders" DETAIL: KEY (id, valid_range)=(1, ["2023-01-01 00:00:00+01","2024-01-01 00:00:00+01")) IS still referenced FROM TABLE "orders".
This is amazing. Thanks a lot to everyone involved in any way.