We have the following partition set up in Oracle which we need to migrate to Postgresql (version 13)-

CREATE TABLE A (
id number(10) not null,
name varchar2(100),
value varchar2(100),
createdat date
constraint a_pk primary key (id))
partition by range (createdat);

CREATE TABLE B (
id number(10) not null,
a_id number(10) not null,
....
....
constraint b_pk primary key (id),
constraint b_a_fk foreign key (a_id) references a (id) on delete cascade
) partition by reference (b_a_fk)

Partition by reference is not supported in Postgresql. Could anyone please advise the alternatives to achieve the same in Postgresql. Basically we need to ensure that when older partitions are dropped from both tables, all records in table "B" should get dropped corresponding to related records in "A".

CodePudding user response：

You have to keep a redundant copy of createdat in b so that you can use it as partitioning key.

To make sore that the related dates are the same, consider the following idea:

• you cannot have id as a primary key, since it does not contain the partitioning key createdat

• so instead use (id, createdat) as primary key of a

• then you can define the foreign key on b on (a_id, createdat), which will automatically guarantee that the related dates are identical

Sure, that solution is not perfect – in particular, you cannot guarantee uniqueness of id. But I think it is the best you can have.