Re: [GENERAL] Constraints of view attributes

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