Re: cataloguing NOT NULL constraints - Mailing list pgsql-hackers

From Dean Rasheed
Subject Re: cataloguing NOT NULL constraints
Date
Msg-id CAEZATCUcy9reXj_M6O490NzWyebaidN7nKe6GTOyTW80fr+7+A@mail.gmail.com
Whole thread Raw
In response to Re: cataloguing NOT NULL constraints  (Alvaro Herrera <alvherre@alvh.no-ip.org>)
Responses Re: cataloguing NOT NULL constraints
List pgsql-hackers
On Mon, 24 Jul 2023 at 17:42, Alvaro Herrera <alvherre@alvh.no-ip.org> wrote:
>
> On 2023-Jul-24, Dean Rasheed wrote:
>
> > Something else I noticed: the error message from ALTER TABLE ... ADD
> > CONSTRAINT in the case of a duplicate constraint name is not very
> > friendly:
> >
> > ERROR:  duplicate key value violates unique constraint
> > "pg_constraint_conrelid_contypid_conname_index"
> > DETAIL:  Key (conrelid, contypid, conname)=(16540, 0, nn) already exists.
> >

To reproduce this error, try to create 2 constraints with the same
name on different columns:

create table foo(a int, b int);
alter table foo add constraint nn not null a;
alter table foo add constraint nn not null b;

I found another, separate issue:

create table p1(a int not null);
create table p2(a int);
create table foo () inherits (p1,p2);
alter table p2 add not null a;

ERROR:  column "a" of table "foo" is already NOT NULL

whereas doing "alter table p2 alter column a set not null" works OK,
merging the constraints as expected.

Regards,
Dean



pgsql-hackers by date:

Previous
From: Nathan Bossart
Date:
Subject: Re: Inefficiency in parallel pg_restore with many tables
Next
From: Pavel Stehule
Date:
Subject: Re: proposal: psql: show current user in prompt