0

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?

Coodie_d
  • 35
  • 6

1 Answers1

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.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • Is there a difference in performance between your suggestion and doing the partitioning by `list` on a `varchar` column ? – Coodie_d May 26 '20 at 12:21
  • 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. – Laurenz Albe May 26 '20 at 12:25
  • So there would be no other solution than adding a new column (or splitting start_date)? No matter how this new column is formatted. – Coodie_d May 26 '20 at 12:35
  • I cannot think of a better solution. That doesn't imply that no such solution exists. – Laurenz Albe May 26 '20 at 12:44