How to find in postgres whether a view column is nullable or not-CodePudding

In postgres is there way to form a query using system tables where we pass the view name and column name and it gives whether that column nullable.

CodePudding user response：

Appreciate that a column appearing inside is a view is backed by a column in the underlying table behind the view. So, if we can determine whether a table's column is nullable, it should suffice for your question. We can try the following query:

SELECT COLUMN_NAME, IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'yourTable';

If you don't know the table name, you may run the following command from the Postgres command line:

\sv your_view_name_here

CodePudding user response：

There is no easy way to do that. View definitions are stored as parsed trees (data type pg_node_tree), and there is no good way to handle these in SQL.

For the related question why view columns are always nullable in PostgreSQL, see this question.

You could write a C extension that analyzes the view definition, and if a column is defined from a table column and there are no outer joins, you could deduce the nullability of the view column from that of the table column. But that is not trivial.

CodePudding user response：

Here is a "trail and error" approach that may help (SQLi unsafe).
Assuming that the view (or table) has an unique id column and at least one row then try to update and see what happens. The test table, view and procedure are defined as temporary in the illustration below.

-- prepare the test ground
create temporary table t (id integer not null, v integer);
create temporary view tv as select * from t;
insert into tv values (1, 2), (2, null);

create or replace procedure pg_temp.is_nullable(tabname text, colname text) as
$$
declare 
  dynsql_template constant text := 
      'update %1$I set %2$I = null where id = (select id from %1$I limit 1)';
begin
  execute format(dynsql_template, tabname, colname);
  rollback;
end;
$$ language plpgsql;

-- test
call pg_temp.is_nullable('tv', 'id'); -- bangs, SQL Error [23502]
call pg_temp.is_nullable('tv', 'v');  -- completes successfully