Find required (non nullable) columns in PostgreSQL database

The query below lists all non-nullable columns in a database.


select col.table_schema,
       case when col.numeric_precision is not null
            then col.numeric_precision
            else col.character_maximum_length 
       end as max_length,
       case when col.datetime_precision is not null
            then col.datetime_precision
            when col.numeric_scale is not null
            then col.numeric_scale
            else 0 
       end as precision
from information_schema.columns col
where col.is_nullable = 'NO'
      and col.table_schema not in ('pg_catalog', 'information_schema')
order by col.column_name;


  • table_schema - name of the table schema
  • table_name - name of the table
  • column_name - name of the column
  • data_type - column data type
  • max_length - maximum length of the data type
  • precision - For numeric types the scale indicates the number of significant digits to the right of the decimal point. And for datetime type indicate the fractional seconds precision of the type for this column


  • One row: represents one non-nullable column in a table
  • Scope of rows: all non-nullable columns in all tables
  • Ordered by: column name

Sample results

Sample results of non-nullable columns in the pagila database (schema).

Comments are only visible when the visitor has consented to statistics cookies. To see and add comments please accept statistics cookies.
There are no comments. Click here to write the first comment.