Re: Wrong note in the information schema section?

On Tue, Aug 31, 2021 at 7:43 AM Pantelis Theodosiou <ypercube(at)gmail(dot)com>
wrote:

>
>
> On Tue, Aug 31, 2021 at 6:53 AM David G. Johnston <
> david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
>
>> On Monday, August 30, 2021, David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com>
>> wrote:
>>
>>> On Monday, August 30, 2021, Daniel Westermann (DWE) <
>>> daniel(dot)westermann(at)dbi-services(dot)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.
>>>
>>>
>>
>> And your error is actually because the name of the unique index backing
>> the constraint is a problem, not the name of the constraint itself. Try
>> naming a check constraint.
>>
>> David J.
>>
>>
>
> As the rest of the paragraph explains, name duplication is checked at
> table level for all constraints and at schema level for index-based
> constraints (UNIQUE, PRIMARY KEY, EXCLUDE):
>
> > > ... However, this extra freedom does not exist for index-based
> constraints (UNIQUE, PRIMARY KEY, and EXCLUDE constraints), because the
> associated index is named the same as the constraint, and index names must
> be unique across all relations within the same schema.
>
> So, adding two index-based constraints (UNIQUE or PK or EXCLUDE) with same
> name fails, whether they are in the same table or different ones in the
> same schema.
> Adding two constraints (whatever type) with same name in the same table
> fails.
> Adding two or more constraints with same name in different tables of the
> same schema succeeds as long as none or only one is index-based.
>
> Best regards,
> Pantelis Theodosiou
>

I should add that the above is in the CREATE TABLE page, not in
(information-schema page):

> Constraint Naming

> The SQL standard says that table and domain constraints must have names
that are unique across the schema containing the table or domain. PostgreSQL is
laxer: it only requires constraint names to be unique across the
constraints attached to a particular table or domain. However, this extra
freedom does not exist for index-based constraints (UNIQUE, PRIMARY KEY,
and EXCLUDE constraints), because the associated index is named the same as
the constraint, and index names must be unique across all relations within
the same schema.