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';




No comments:

Post a Comment

Please do not enter any spam link in the comment box.

NUnit : Introduction

There are several unit test tools in the market, but one of them is NUnit, the most popular tool for doing unit testing. So in this tutorial...