Re: cataloguing NOT NULL constraints

On Mon, 24 Jul 2023 at 17:42, Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)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