Horton
Menu

Re: Self referential foreign keys in partitioned table not working as expected

Christoph Berg <myon@debian.org>

View thread
From: Christoph Berg <myon@debian.org>
To: Luca Vallisa <luca.vallisa@gmail.com>
Cc: pgsql-bugs@lists.postgresql.org
Date: 2025-04-01T10:46:02Z
Lists: pgsql-bugs, pgsql-hackers 
Re: Luca Vallisa
> The provided version throws an error.

Ok, I can confirm this.

This throws an error like it should:

create table test (
    id_1 int4 not null,
    id_2 int4 not null,
    parent_id_2 int4 null,
    primary key (id_1, id_2),
    foreign key (id_1, parent_id_2) references test (id_1, id_2)
);
insert into test values (1, 1, null), (1, 2, 1);
delete from test where (id_1, id_2) = (1, 1);

On a partitioned table, it does not throw the error:

create table test (
    id_1 int4 not null,
    id_2 int4 not null,
    parent_id_2 int4 null,
    primary key (id_1, id_2),
    foreign key (id_1, parent_id_2) references test (id_1, id_2)
) partition by list (id_1);
create table test_1 partition of test for values in (1);
insert into test values (1, 1, null), (1, 2, 1);
delete from test where (id_1, id_2) = (1, 1);

Christoph

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Handle self-referencing FKs correctly in partitioned tables

  2. Restructure foreign key handling code for ATTACH/DETACH

  3. Fix self-referencing foreign keys with partitioned tables