Обсуждение: [SQL] Database views metadata always nullable columns
Hi...
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.
I'm aware that pg_attributes.attnotnull is updatable reflecting the changes in information_schema also, but, do you know of some SQL query to extract this information correctly automatically?
Regards,
basuraspam - <basuraspam0@gmail.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 | YESf2 | NO
(2 rows)
regression=# select attname,attnotnull from pg_attribute where attrelid = 'foo'::regclass and attnum > 0;attname |
attnotnull
---------+------------f1 | ff2 | 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
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@sss.pgh.pa.us>:
basuraspam - <basuraspam0@gmail.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
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:
Since view columns cannot be specified NOT NULL (or have their own constraints for that matter) reporting false here is accurate.
That we don't parse the view and attempt to derive constraints from the underlying query and tables, if any, is unsurprising.
i.e., should "CREATE VIEW test (col1) AS SELECT '1'::col1;
report NOT NULL for test.col1?
About the only SQL-visible automated way to do what you describe, to some level of accuracy, is to EXPLAIN the view and extract the tables and columns and look them up. That will fail for, say, SELECT * FROM tbl1 LEFT JOIN tbl2, when looking at properties of columns from tbl2.
David J.
basuraspam - <basuraspam0@gmail.com> writes:
> 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;
Ah, gotcha. No, sorry, we do not track nullability of view columns as
such.
regards, tom lane
Thanks guys for your feedback... I'll try to dig a bit more about how to setup the EXPLAIN view "trick" scenario...
Regards,
2017-06-02 18:07 GMT+02:00 Tom Lane <tgl@sss.pgh.pa.us>:
basuraspam - <basuraspam0@gmail.com> writes:
> 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;
Ah, gotcha. No, sorry, we do not track nullability of view columns as
such.
regards, tom lane