agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedDatabase views metadata always nullable columns
6+ messages / 3 participants
[nested] [flat]
* Database views metadata always nullable columns
@ 2017-06-02 14:11 basuraspam - <basuraspam0@gmail.com>
2017-06-02 14:21 ` Re: Database views metadata always nullable columns Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 6+ messages in thread
From: basuraspam - @ 2017-06-02 14:11 UTC (permalink / raw)
To: pgsql-sql
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,
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Database views metadata always nullable columns
2017-06-02 14:11 Database views metadata always nullable columns basuraspam - <basuraspam0@gmail.com>
@ 2017-06-02 14:21 ` Tom Lane <tgl@sss.pgh.pa.us>
2017-06-02 15:33 ` Re: Database views metadata always nullable columns basuraspam - <basuraspam0@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Tom Lane @ 2017-06-02 14:21 UTC (permalink / raw)
To: basuraspam - <basuraspam0@gmail.com>; +Cc: pgsql-sql
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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Database views metadata always nullable columns
2017-06-02 14:11 Database views metadata always nullable columns basuraspam - <basuraspam0@gmail.com>
2017-06-02 14:21 ` Re: Database views metadata always nullable columns Tom Lane <tgl@sss.pgh.pa.us>
@ 2017-06-02 15:33 ` basuraspam - <basuraspam0@gmail.com>
2017-06-02 15:59 ` Re: Database views metadata always nullable columns David G. Johnston <david.g.johnston@gmail.com>
2017-06-02 16:07 ` Re: Database views metadata always nullable columns Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 6+ messages in thread
From: basuraspam - @ 2017-06-02 15:33 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
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
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Database views metadata always nullable columns
2017-06-02 14:11 Database views metadata always nullable columns basuraspam - <basuraspam0@gmail.com>
2017-06-02 14:21 ` Re: Database views metadata always nullable columns Tom Lane <tgl@sss.pgh.pa.us>
2017-06-02 15:33 ` Re: Database views metadata always nullable columns basuraspam - <basuraspam0@gmail.com>
@ 2017-06-02 15:59 ` David G. Johnston <david.g.johnston@gmail.com>
1 sibling, 0 replies; 6+ messages in thread
From: David G. Johnston @ 2017-06-02 15:59 UTC (permalink / raw)
To: basuraspam - <basuraspam0@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-sql
On Fri, Jun 2, 2017 at 8:33 AM, basuraspam - <basuraspam0@gmail.com> wrote:
> 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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Database views metadata always nullable columns
2017-06-02 14:11 Database views metadata always nullable columns basuraspam - <basuraspam0@gmail.com>
2017-06-02 14:21 ` Re: Database views metadata always nullable columns Tom Lane <tgl@sss.pgh.pa.us>
2017-06-02 15:33 ` Re: Database views metadata always nullable columns basuraspam - <basuraspam0@gmail.com>
@ 2017-06-02 16:07 ` Tom Lane <tgl@sss.pgh.pa.us>
2017-06-02 19:30 ` Re: Database views metadata always nullable columns basuraspam - <basuraspam0@gmail.com>
1 sibling, 1 reply; 6+ messages in thread
From: Tom Lane @ 2017-06-02 16:07 UTC (permalink / raw)
To: basuraspam - <basuraspam0@gmail.com>; +Cc: pgsql-sql
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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Database views metadata always nullable columns
2017-06-02 14:11 Database views metadata always nullable columns basuraspam - <basuraspam0@gmail.com>
2017-06-02 14:21 ` Re: Database views metadata always nullable columns Tom Lane <tgl@sss.pgh.pa.us>
2017-06-02 15:33 ` Re: Database views metadata always nullable columns basuraspam - <basuraspam0@gmail.com>
2017-06-02 16:07 ` Re: Database views metadata always nullable columns Tom Lane <tgl@sss.pgh.pa.us>
@ 2017-06-02 19:30 ` basuraspam - <basuraspam0@gmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: basuraspam - @ 2017-06-02 19:30 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
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
>
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2017-06-02 19:30 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2017-06-02 14:11 Database views metadata always nullable columns basuraspam - <basuraspam0@gmail.com>
2017-06-02 14:21 ` Tom Lane <tgl@sss.pgh.pa.us>
2017-06-02 15:33 ` basuraspam - <basuraspam0@gmail.com>
2017-06-02 15:59 ` David G. Johnston <david.g.johnston@gmail.com>
2017-06-02 16:07 ` Tom Lane <tgl@sss.pgh.pa.us>
2017-06-02 19:30 ` basuraspam - <basuraspam0@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox