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)