Postgresql v12 reference partitioned table to partitioned table but not with partition key

Question

I've been trying to reference partitioned table in postgresql v12 but got an issue. The tables are partitioned by range on a date column, and the values for each partition are month delimiter (eg: FOR VALUES FROM ('2020-04-01T00:00:00') TO ('2020-05-01T00:00:00'))

I have 2 tables:

Table A
     Column      |           Type           | Collation | Nullable | Default 
-----------------+--------------------------+-----------+----------+---------
 uuid            | character varying(36)    |           | not null | 
 start_date      | timestamp with time zone |           | not null | 
Partition key: RANGE (start_date)
Indexes:
    "table_a_pkey" PRIMARY KEY, btree (uuid, start_date)

Table B
     Column      |           Type           | Collation | Nullable | Default 
-----------------+--------------------------+-----------+----------+---------
 uuid            | character varying(36)    |           | not null | 
 start_date      | timestamp with time zone |           | not null | 
 ref_a_id        | character varying(36)    |           | not null | 
Partition key: RANGE (start_date)
Indexes:
    "table_b_pkey" PRIMARY KEY, btree (uuid, start_date)

When adding a primary key to a partition table, I know that the partition key must be included. However, the table_a.start_date and table_b.start_date don't perfectly match, only the month of both dates should match in general and of course, the ref_a_id to uuid.

So far, this is what i tried:

ALTER TABLE table_a ADD CONSTRAINT ref_fk FOREIGN KEY (ref_a_id) REFERENCES table_b (uuid) DEFERRABLE INITIALLY DEFERRED;
-> ERROR:  there is no unique constraint matching given keys for referenced table "table_b"

ALTER TABLE table_a ADD CONSTRAINT ref_fk FOREIGN KEY (ref_a_id) REFERENCES table_b DEFERRABLE INITIALLY DEFERRED;
-> ERROR:  number of referencing and referenced columns for foreign key disagree

ALTER TABLE table_a ADD CONSTRAINT ref_fk FOREIGN KEY (ref_a_id, start_date) REFERENCES table_b (uuid, start_date) DEFERRABLE INITIALLY DEFERRED;

The latter works, but at import time I got

-> ERROR:  insert or update on table "table_b" violates foreign key constraint "ref_fk"
DETAIL:  Key (ref_a_id, start_date)=(XXXXX-XXXXX-XXXXX-XXXXX, 2020-05-27 01:32:13+00) is not present in table "table_a".

The only solution I see right now, is to add a new column year_month with values like 2020_04 and do the partitioning by list on this new column. Therefore the reference should match.

Is there another solution? Is there a way I could reference only the month of the start_date columns instead of adding a new column? Or even maybe reference only the table_a.uuid?

Laurenz Albe · Accepted Answer · 2020-05-26 11:59:56Z

0

You should split the column in two parts, a year_month of type date that contains the date, rounded to months, and an offset of type interval that stores the difference to start_date. Then you can use the first column in your constraint.

answered May 26, 2020 at 11:59

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Coodie_d Over a year ago

Is there a difference in performance between your suggestion and doing the partitioning by list on a varchar column ?

Laurenz Albe Over a year ago

Never, never store dates in a string column. You are missing out on value checking, date arithmetic and waste storage space to boot. So yes, this can be performance relevant, but that's not the main issue.

Coodie_d Over a year ago

So there would be no other solution than adding a new column (or splitting start_date)? No matter how this new column is formatted.

Laurenz Albe Over a year ago

I cannot think of a better solution. That doesn't imply that no such solution exists.

Collectives™ on Stack Overflow

Postgresql v12 reference partitioned table to partitioned table but not with partition key

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related