Search Postgresql Archives

Two questions about "pg_constraint"

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



*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?

*Question 2: what happened to the column "consrc"?*

The PG 11 account of "pg_constraint"

describes "consrc" (text) thus:

« If a check constraint, a human-readable representation of the _expression_ »

Ad hoc queries in my PG 11.9 env show results like « (v = lower(v)) » in this column for my tables. This is useful information. But the PG 14 version of "pg_constraint" has no such column (and nor does the doc mention it). Is this information now exposed somewhere else?


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux