Menu
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.
335
You can make that column in your view non-nullable by replacing it with ISNULL(CONVERT(BIT,U. RETIRED),0) AS Retired. If U. RETIRED was not null to start, it functionally doesn't change anything except the column in the view.
By default, a column can hold NULL values. The NOT NULL constraint enforces a column to NOT accept NULL values. This enforces a field to always contain a value, which means that you cannot insert a new record, or update a record without adding a value to this field.
It's a boolean flag to tell if the columns is nullable (aka it can contains null). If nullable = 1 the column can contains null. Follow this answer to receive notifications.
You must therefore use NOT NULL for all columns that cannot legitimately contain nulls. If you specify that a column is NOT NULL , you are defining a constraint that ensures that that the column can never hold or accept NULL , so you can't accidentally leave the value out.
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.)
185
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.
39
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
