Why are the columns of my view nullable?

Question

I am running PostgreSQL 9.2 on Windows.

I have an existing table with some non-null columns:

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

I create a view in this table:

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

I am surprised that in reports report_schema.columns for reports of the form is_nullable is 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 this expected behavior?

My problem is that I am trying to import such views into the 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-Katkoll suggested, I could manually set them to true:

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

And this change is reflected in information_commands:

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

Output:

 "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 will try to import views into the Entity Framework data model.

EDIT 2 :

As already mentioned, it works, the view is now correctly imported into the Entity Framework data model. Of course, I will not set all columns to be invalid, as shown above, only those that cannot be zeroed out in the base table.

Answer 1

I believe that this is the expected behavior, but I do not pretend to fully understand it. The columns in the base table seem to have the correct attributes.

The column in the system tables underlying the schema information here seems to be "attrnotnull". I see only one thread related to "attnotnull" on the pgsql-hackers mailing list: cataloging NOT NULL restrictions . (But this column may have had a different name in the earlier version. Probably worth exploring.)

You can see the behavior of this request. You will 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 with primary key constraints and columns with NOT NULL constraint have an attnotnull value of t. The same columns in the view have "attnotnull" set to "f".

If you tilt your head and squint to the right, it makes sense. The column in the view is not declared NOT NULL. Only the column in the base table.

The pg_attribute.attnotnull column is updated. You can set it to TRUE, and this change is apparently reflected in the information_schema views. Although you can set it to TRUE directly, I think it would be more convenient for me to set it according to the value in the base table, (And more comfortable, I do not want to mean that I am comfortable at all with system tables.)

Answer 2

PostgreSQL's uncertainty tracking is not very advanced. In most cases, it will by default state that everything could potentially be invalid, which in many cases is allowed by the relevant standards. This also holds true here: Nullability is not tracked through views. I would not rely on it for the application.