Thread: Wrong note in the information schema section?

Hi %.

we have this note in the information schema section, e.g. in
https://www.postgresql.org/docs/current/information-schema.html

..."This is because the SQL standard requires constraint names to be unique within a schema, but PostgreSQL does not
enforcethis restriction."  
...
PostgreSQL does enforce unique constraint names in a schema:

postgres=# create table t1 ( a int );
CREATE TABLE
postgres=# alter table t1 add constraint c1 unique (a);
ALTER TABLE
postgres=# alter table t1 add constraint c1 unique (a);
ERROR:  relation "c1" already exists

Am I reading this wrong? I know you can have the same constraint with different names:

postgres=# create table t1 ( a int );
CREATE TABLE
postgres=# alter table t1 add constraint c2 unique (a);
ALTER TABLE
postgres=# alter table t1 add constraint c3 unique (a);
ALTER TABLE

... but I guess this is not what the notes is supposed to tell me, correct?

Regards
Daniel

>>..."This is because the SQL standard requires constraint names to be unique within a schema, but PostgreSQL does not
enforcethis >>restriction."  
>>..
>>PostgreSQL does enforce unique constraint names in a schema:

>>[...]
>>... but I guess this is not what the notes is supposed to tell me, correct?


>Practically speaking there must be some level of scope where a duplicate name error can occur.  All the docs say is
thatthe schema >scope is not it.  You've demonstrated that it is the table scope where duplication of names is
detected.

Thanks, David. The sentence above is still misleading, at least according to my understanding.

Regards
Daniel

>Practically speaking there must be some level of scope where a duplicate name error can occur.  All the docs say is that the schema >scope is not it.  You've demonstrated that it is the table scope where duplication of names is detected.

Thanks, David. The sentence above is still misleading, at least according to my understanding.

On Monday, August 30, 2021, Daniel Westermann (DWE) <daniel.westermann@dbi-services.com> wrote:

>Practically speaking there must be some level of scope where a duplicate name error can occur.  All the docs say is that the schema >scope is not it.  You've demonstrated that it is the table scope where duplication of names is detected.

Thanks, David. The sentence above is still misleading, at least according to my understanding.

Create a second table and add a constraint of the same name to it.

 

 
On Monday, August 30, 2021, David G. Johnston <david.g.johnston@gmail.com> wrote:
On Monday, August 30, 2021, Daniel Westermann (DWE) <daniel.westermann@dbi-services.com> wrote:

>>>Practically speaking there must be some level of scope where a duplicate name error can occur.  All the docs say is
thatthe schema >>>scope is not it.  You've demonstrated that it is the table scope where duplication of names is
detected.

>>Thanks, David. The sentence above is still misleading, at least according to my understanding.

>Create a second table and add a constraint of the same name to it.
 

>And your error is actually because the name of the unique index backing the constraint is a problem, not the name of
theconstraint >itself.  Try naming a check constraint. 

Thanks, now it makes sense:

postgres=# create table t1 ( a int );
CREATE TABLE
postgres=# create table t2 ( a int );
CREATE TABLE
postgres=# alter table t1 add constraint chk1 check ( a > 1 );
ALTER TABLE
postgres=# alter table t2 add constraint chk1 check ( a > 1 );


Regards
Daniel