PostgreSQL: Can I determine if a field in a view is nullable?

Problem: Suppose I have a table "Employee"

CREATE TABLE employee (        id integer not null,        first_name text not null,        middle_name text null,        last_name text not null );

Now I have created a VIEW as below

CREATE VIEW vw_employee AS SELECT   id,   first_name as fname,   middle_name as mname,   last_name as lname FROM employee;

Is there any way that I can determine by looking at the tables that define this view in my schema whether or not “mname” can be null?

Solution: No, at least, not generally. That the view column is a direct pass-through of a non-nullable column is eroded away and all view columns 'smell' nullable.

Run the following query it will returns the result-set of the field where you can see it is nullable or not.

SELECT column_name, is_nullable from information_schema.columns WHERE table_name = 'employee';

Now run the following query, you will see in the result all fields are nullable type.

SELECT column_name, is_nullable from information_schema.columns WHERE table_name = 'vw_employee';