Why are my view's columns nullable?

Question

I'm running PostgreSQL 9.2 on Windows.

I have an existing table with some non nullable columns :

CREATE TABLE testtable
(
  bkid serial NOT NULL,
  bklabel character varying(128),
  lacid integer NOT NULL
}

The I create a view on this table :

CREATE OR REPLACE VIEW test AS
SELECT testtable.bkid, testtable.lacid
from public.testtable;

I'm surprised that information_schema.columns for the view reports is_nullable to be YES for the selected columns ?

select * from information_schema.columns where table_name = 'test'

Reports :

"MyDatabase";"public";"test";"bkid";1;"";"YES";"integer";;;32;2;0;;"";;"";"";"";"";"";"";"";"";"";"MyDatabase";"pg_catalog";"int4";"";"";"";;"1";"NO";"NO";"";"";"";"";"";"";"NEVER";"";"NO"
"MyDatabase";"public";"test";"lacid";2;"";"YES";"integer";;;32;2;0;;"";;"";"";"";"";"";"";"";"";"";"MyDatabase";"pg_catalog";"int4";"";"";"";;"2";"NO";"NO";"";"";"";"";"";"";"NEVER";"";"NO"

Is it an expected behavior ?

My problem is that I'm trying to import such views in an Entity Framework Data Model and it fails because all columns are marked as nullable.

EDIT 1 :

The following query :

select attrelid, attname, attnotnull, pg_class.relname
from pg_attribute
inner join pg_class on attrelid = oid
where relname = 'test'

returns :

attrelid;attname;attnotnull;relname
271543;"bkid";f;"test"
271543;"lacid";f;"test"

As expected, attnotnull is 'false'.

As @Mike-Sherrill-Catcall suggested, I could manually set them to true :

update pg_attribute
set attnotnull = 't'
where attrelid = 271543

And the change is reflected in the information_schema.columns :

select * from information_schema.columns where table_name = 'test'

Output is :

"MyDatabase";"public";"test";"bkid";1;"";"NO";"integer";;;32;2;0;;"";;"";"";"";"";"";"";"";"";"";"MyDatabase";"pg_catalog";"int4";"";"";"";;"1";"NO";"NO";"";"";"";"";"";"";"NEVER";"";"NO"
"MyDatabase";"public";"test";"lacid";2;"";"NO";"integer";;;32;2;0;;"";;"";"";"";"";"";"";"";"";"";"MyDatabase";"pg_catalog";"int4";"";"";"";;"2";"NO";"NO";"";"";"";"";"";"";"NEVER";"";"NO"

I'll try to import the views in the Entity Framework data model.

EDIT 2 :

As guessed, it works, the view is now correctly imported in the Entity Framework Data Model. Of course, I won't set all columns to be non nullable, as demonstrated above, only those non nullable in the underlying table.

Answer 1

I believe this is expected behavior, but I don't pretend to fully understand it. The columns in the base table seem to have the right attributes.

The column in the system tables underlying the information_schema here seems to be "attrnotnull". I see only one thread referring to "attnotnull" on the pgsql-hackers listserv: cataloguing NOT NULL constraints. (But that column might have had a different name in an earlier version. It's probably worth researching.)

You can see the behavior with this query. You'll need to work with the WHERE clause to get exactly what you need to see.

select attrelid, attname, attnotnull, pg_class.relname
from pg_attribute
inner join pg_class on attrelid = oid
where attname like 'something%'

On my system, columns that have a primary key constraint and columns that have a NOT NULL constraint have "attnotnull" set to 't'. The same columns in a view have "attnotnull" set to 'f'.

If you tilt your head and squint just right, that kind of makes sense. The column in the view isn't declared NOT NULL. Just the column in the base table.

The column pg_attribute.attnotnull is updatable. You can set it to TRUE, and that change seems to be reflected in the information_schema views. Although you can set it to TRUE directly, I think I'd be more comfortable setting it to match the value in the base table. (And by more comfortable, I don't mean to imply I'm comfortable at all with mucking about in the system tables.)

Answer 2

Why:

A view could be computed but references the column from a table. That computation could result in a NULL value on what is otherwise a non-null column. So basically, they put it into the too hard basket.

There is a way to see the underlying nullability for yourself with the following query:

select vcu.column_name, c.is_nullable, c.data_type
from information_schema.view_column_usage vcu 
join information_schema."columns" c 
    on c.column_name = vcu.column_name 
    and c.table_name  = vcu.table_name 
    and c.table_schema  = vcu.table_schema 
    and c.table_catalog = vcu.table_catalog 
where view_name = 'your_view_here'

If you know that you are only projecting the columns raw without functions, then it will work. Ideally, the Postgres provider for EF would use this view and also read the view definition to confirm nullability.