PostgreSQL 11 foreign key on partitioning tables

Question

In the PostgreSQL 11 Release Notes I found the following improvements to partitioning functionality:

• Add support for PRIMARY KEY, FOREIGN KEY, indexes, and triggers on partitioned tables

I need this feature and tested it.

Create table:

CREATE TABLE public.tbl_test
(
    uuid character varying(32) NOT null,
    registration_date timestamp without time zone NOT NULL    
)
PARTITION BY RANGE (registration_date);

Try to create Primary key:

ALTER TABLE public.tbl_test ADD CONSTRAINT pk_test PRIMARY KEY (uuid);

I get an error SQL Error [0A000]. If use composite PK (uuid, registration_date) then it's work. Because PK contains partitioning column

Conclusion: create PK in partitioning tables work with restrictions (PK need contains partitioning column).

Try to create Foreign key

CREATE TABLE public.tbl_test2
(
    uuid character varying(32) NOT null,
    test_uuid character varying(32) NOT null
);

ALTER TABLE tbl_test2
   ADD CONSTRAINT fk_test FOREIGN KEY (test_uuid)
   REFERENCES tbl_test (uuid);

I get an error SQL Error [42809]. It means FOREIGN KEY on partitioning tables not work.

Maybe i'm doing something wrong. Maybe somebody tried this functionality and know how this work. Maybe somebody know workaround except implement constraint in the application.

Answer 1

PostgreSQL v12.0 will probably support foreign keys that reference partitioned tables. But this is still not guaranteed as v12.0 is still in development.

For v11 and lower versions, you may use triggers as described by depesz in these posts: part1, part2, and part3.

Update: PostgreSQL v12.0 was released on Oct 3, 2019, with this feature included