Table of Contents:


    Follow us

    Check if is column nullable

    This applies to PostgreSQL. Article available also for: Oracle database IBM Db2 Amazon Redshift Snowflake

    This query returns nullability information of the specified column.

    Query

    select c.table_schema,
           c.table_name,
           c.column_name,
           case c.is_nullable
                when 'NO' then 'not nullable'
                when 'YES' then 'is nullable'
           end as nullable
    from information_schema.columns c
    join information_schema.tables t
         on c.table_schema = t.table_schema 
         and c.table_name = t.table_name
    where c.table_schema not in ('pg_catalog', 'information_schema')
          and t.table_type = 'BASE TABLE' 
    order by table_schema,
             table_name,
             column_name;
    

    Columns

    • table_schema - name of schema
    • table_name - name of table
    • column_name - name of column
    • nullable - nullability attribute for the column:
      • is nullable - is nullable
      • not nullable - is not nullable

    Rows

    • One row nullability of the specified column
    • Scope of rows - all columns in the database
    • Order by - table schema, table_name, column name

    Sample results

    Share this article

    Comments (0)