Find required (non nullable) columns in Redshift

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

Query

select col.table_schema,
       col.table_name,
       col.column_name,
       col.data_type,
       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;

Columns

  • 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

Rows

  • 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

0
There are no comments. Click here to write the first comment.