Re: cataloguing NOT NULL constraints

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 match the error message for other constraint types, this should be:
>
> ERROR: constraint "nn" for relation "foo" already exists

Hmm, how did you get this one? I can't reproduce it:

55490 17devel 3166154=# create table foo (a int constraint nn not null);
CREATE TABLE
55490 17devel 3166154=# alter table foo add constraint nn not null a;
ERROR: column "a" of table "foo" is already NOT NULL

55490 17devel 3166154=# drop table foo;
DROP TABLE

55490 17devel 3166154=# create table foo (a int);
CREATE TABLE
Duración: 1,472 ms
55490 17devel 3166154=# alter table foo add constraint nn not null a, add constraint nn not null a;
ERROR: column "a" of table "foo" is already NOT NULL

--
Álvaro Herrera Breisgau, Deutschland — https://www.EnterpriseDB.com/
"El número de instalaciones de UNIX se ha elevado a 10,
y se espera que este número aumente" (UPM, 1972)