Thread: [GENERAL] Constraints of view attributes

Hi,

create table t1 (f1 text not null);
create table t2 (f2 text);
create view v as (select t1.f1, t2.f2 from t1, t2);

Given v, How can a find out the t1.f1 has a not null constraint.
I can easily find this for t1 in the system catalog, but not for v.
I learned on IRC today that this should be somewhere in pg_rewrite but
where exactly and how do I get this information? Parse rewrite
query_tree? How?

--
Greg

On 05/11/2017 12:24 PM, 51183341@gmx.at wrote:
> Hi,
>
> create table t1 (f1 text not null);
> create table t2 (f2 text);
> create view v as (select t1.f1, t2.f2 from t1, t2);
>
> Given v, How can a find out the t1.f1 has a not null constraint.
> I can easily find this for t1 in the system catalog, but not for v.
> I learned on IRC today that this should be somewhere in pg_rewrite but
> where exactly and how do I get this information? Parse rewrite
> query_tree? How?

SELECT
     table_name, column_name, is_nullable
FROM
     information_schema.columns
WHERE
     table_name IN (
         SELECT
             table_name
         FROM
             information_schema.view_column_usage
         WHERE
             view_name = 'v'
             AND column_name = 'f1')
         AND column_name = 'f1';

  table_name | column_name | is_nullable
------------+-------------+-------------
  t1         | f1          | NO

>
> --
> Greg
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com

<51183341@gmx.at> writes:
> create table t1 (f1 text not null);
> create table t2 (f2 text);
> create view v as (select t1.f1, t2.f2 from t1, t2);

> Given v, How can a find out the t1.f1 has a not null constraint.

I assume what you actually mean is you want to know whether the
view v will always produce a non-null in that column.  That's not
that easy.  It is true in the above example, but consider for instance

create view v as select t1.f1, t2.f2 from t2 left join t1 on ...

With the outer join, that view column could produce nulls despite
the NOT NULL constraint on t1.  There are other constructs such
as GROUPING SETS that break the equivalence, too.  So you'd need a
fairly careful inspection of the view parsetree not only to find
which table is referenced, but whether there's something in the
view that defeats the deduction you'd like to make.  There isn't
anything in Postgres right now that makes that type of inference,
let alone a way to export it to userland.

            regards, tom lane