Re: Constraints of view attributes

On 05/11/2017 12:24 PM, 51183341(at)gmx(dot)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(dot)klaver(at)aklaver(dot)com