Re: Two questions about "pg_constraint"

On 8/24/22 13:11, Bryn Llewellyn wrote:
> *Question 1: why does "pg_constraint" have a "connamespace" column?*
> 
> I created this temporary view (using PG 14.4):
> 
> *create temporary view all_constraints(t_owner, t_schema, t_name, 
> c_name, same) as
> select
>    r.rolname,
>    s.nspname,
>    c.relname,
>    x.conname,
>    (x.connamespace = c.relnamespace)
> from
>    pg_class c
>    inner join
>    pg_roles r
>    on c.relowner = r.oid
>    inner join
>    pg_namespace s
>    on c.relnamespace = s.oid
>    inner join pg_constraint x
>    on c.oid = x.conrelid
> where c.relkind = 'r';
> *
> 
> I created three tables, each with a user-created constraint. The tables 
> also have implicitly created primary key constraints.
> 
> Then I did this:
> 
> *select count(*) from all_constraints;
> *
> It said that the count is over a hundred. (All but the rows for my three 
> tables are for rows for tables in the "pg_catalog" schema.)
> 
> Then I did this:
> 
> *select exists(select 1 from all_constraints where not same)::text;
> *
> It said "false".
> 
> Over one hundred seems to be a fair sample size. So it seems to be 
> reasonable to assume that "pg_constraint.connamespace = 
> pg_class.relnamespace" is always true. Ordinary common-sense analysis of 
> the query suggests this too. If the hypothesis is right, then 
> "connamespace" is simply a derived value. And this would be a departure 
> from usual table design practice.
> 
> What do you think?
> 

create table c1 (id integer, constraint pk1 primary key(id));
CREATE TABLE

create table c2 (id integer, constraint pk1 primary key(id));
ERROR:  relation "pk1" already exists


create table test.c2 (id integer, constraint pk1 primary key(id));
CREATE TABLE

  select conname, connamespace from pg_constraint where conname = 'pk1';
  conname | connamespace
---------+--------------
  pk1     |         2200
  pk1     |        59706


From:

https://www.postgresql.org/docs/current/catalog-pg-constraint.html

conname name

Constraint name (not necessarily unique!)

So connamespace makes it unique.

-- 
Adrian Klaver
adrian.klaver@aklaver.com