Content-Type: text/plain; charset="utf-8"
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Subject: BUG #19507: Auto-named partition table constraint conflicts
To: pgsql-bugs@lists.postgresql.org
From: PG Bug reporting form <noreply@postgresql.org>
Cc: markoog@gmail.com
Reply-To: markoog@gmail.com, pgsql-bugs@lists.postgresql.org
Date: Thu, 04 Jun 2026 07:37:13 +0000
Message-ID: <19507-78eec77213b5284f@postgresql.org>
Auto-Submitted: auto-generated
Archived-At: 
 <https://www.postgresql.org/message-id/19507-78eec77213b5284f%40postgresql.org>
Precedence: bulk

The following bug has been logged on the website:

Bug reference:      19507
Logged by:          Marko Grujic
Email address:      markoog@gmail.com
PostgreSQL version: 18.4
Operating system:   MacOS, Debian
Description:       =20

Hi all,

I've stumbled on a peculiar class of edge cases involving partitioned
tables, where at least
one of the partitions is in a different schema from the parent, and
auto-named constraints.
I believe this can be classified as a bug, and probably warrants a fix.

I'm seeing this on PG18 (18.4, official Docker image):

postgres=3D# select version();
                                                         version
---------------------------------------------------------------------------=
-----------------------------------------------
 PostgreSQL 18.4 (Debian 18.4-1.pgdg13+1) on aarch64-unknown-linux-gnu,
compiled by gcc (Debian 14.2.0-19) 14.2.0, 64-bit
(1 row)

 I'm also hitting it on latest master too (19beta1).

The repro is quite simple, and can be reduced to 5 SQL statements:

postgres=3D# create schema partitions;
create table t(a int) partition by range (a);
create table partitions.t_1_10 partition of t for values from (1) to (10);
alter table partitions.t_1_10 add constraint t_a_not_null check (a is not
null);
alter table t alter column a set not null;
CREATE SCHEMA
CREATE TABLE
CREATE TABLE
ALTER TABLE
ERROR:  42710: constraint "t_a_not_null" for relation "t_1_10" already
exists
LOCATION:  AddRelationNewConstraints, heap.c:2666

Note that the last ALTER, which creates auto-named constraints, leads to an
error, because a preceding ALTER,
which created a user-named constraint, already picked the default name that
the auto-generated name would use.

Consequently, no new constraints are constructed for any of the tables in
the hierarchy. A workaround could be to
use an explicitly-named variant of the ALTER statement, but that is not
necessarily obvious. In addition the auto-named
variant should really resolve any pre-existing conflicts automatically.

So the exact set of circumstances to hit this bug is slightly convoluted,
but not unrealistic:
1. there's a partitioned table
2. which has at least one partition in another schema
3. that partition has a pre-existing constraint
4. (optional) the constraint was named by the user, and matches what PG
would use by default

Here's a couple more variants of the same issue:

postgres=3D# create schema partitions;
create table t(a int) partition by range (a);
create table partitions.t_1_10 partition of t for values from (1) to (10);
alter table partitions.t_1_10 add constraint t_a_check check (a > 100);
alter table t add check (a > 0);
CREATE SCHEMA
CREATE TABLE
CREATE TABLE
ALTER TABLE
ERROR:  42710: constraint "t_a_check" for relation "t_1_10" already exists
LOCATION:  MergeWithExistingConstraint, heap.c:2792

and

postgres=3D# create schema partitions;
create table t(a int) partition by range (a);
create table partitions.t_1_10 partition of t for values from (1) to (10);
alter table partitions.t_1_10 add constraint t_a_not_null check (a is not
null);
alter table t add not null a;
CREATE SCHEMA
CREATE TABLE
CREATE TABLE
ALTER TABLE
ERROR:  42710: constraint "t_a_not_null" for relation "t_1_10" already
exists
LOCATION:  AddRelationNewConstraints, heap.c:2666

To make matters worse, the same failure mode can be hit without the user
naming the pre-existing constraint explicitly in the first place, for
instance:

postgres=3D# create schema partitions;
create table t(a int) partition by range (a);
create table partitions.t partition of t for values from (1) to (10);
alter table partitions.t add check (a > 1);
alter table t add check (a > 0);
CREATE SCHEMA
CREATE TABLE
CREATE TABLE
ALTER TABLE
ERROR:  42710: constraint "t_a_check" for relation "t" already exists
LOCATION:  MergeWithExistingConstraint, heap.c:2792