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

Postgres 11 only supports foreign keys from a partitioned table to a (non-partitioned) table.

Previously not even that was possible, and that's what the release notes are about.

This limitation is documented in the chapter about partitioning in the manual

While primary keys are supported on partitioned tables, foreign keys referencing partitioned tables are not supported. (Foreign key references from a partitioned table to some other table are supported.

^{(emphasis mine)}