Re: Database views metadata always nullable columns

Hi Tom,
I agree, it would be better including an example. The "issue" I reported
is with database views not with tables. Taking your example as base:

create table foo (f1 int, f2 int not null);
create view foo_view as select * from foo;

select column_name, is_nullable from information_schema.columns where
table_name = 'foo_view';

column_name | is_nullable

-------------+-------------

f1 | YES

f2 | YES

(2 rows)

select attname,attnotnull from pg_attribute where attrelid =
'foo_view'::regclass and attnum > 0;

attname | attnotnull

---------+------------

f1 | f

f2 | f

(2 rows)

2017-06-02 16:21 GMT+02:00 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:

> basuraspam - <basuraspam0(at)gmail(dot)com> writes:
> > Doing some automatic metadata parsing job against postgresql schema I
> > just find that database views always shown all its column as nullable, it
> > doesn't matter what are the constraints in the original table columns
> used
> > as source to create the view.
> > I just checked it against infomation_schema and pg_attribute with the
> same
> > result.
>
> regression=# create table foo (f1 int, f2 int not null);
> CREATE TABLE
> regression=# select column_name, is_nullable from
> information_schema.columns where table_name = 'foo';
> column_name | is_nullable
> -------------+-------------
> f1 | YES
> f2 | NO
> (2 rows)
>
> regression=# select attname,attnotnull from pg_attribute where attrelid =
> 'foo'::regclass and attnum > 0;
> attname | attnotnull
> ---------+------------
> f1 | f
> f2 | t
> (2 rows)
>
> So I don't see anything particularly broken here. Maybe you should
> provide some concrete examples rather than making sweeping claims.
>
> regards, tom lane
>