List table columns in PostgreSQL database

Query below lists all table columns in a database.

Query

select table_schema,
       table_name,
       ordinal_position as position,
       column_name,
       data_type,
       case when character_maximum_length is not null
            then character_maximum_length
            else numeric_precision end as max_length,
       is_nullable,
       column_default as default_value
from information_schema.columns
where table_schema not in ('information_schema', 'pg_catalog')
order by table_schema, 
         table_name,
         ordinal_position;

Columns

  • table_schema - schema name
  • table_name - table name
  • position - position of column in the table
  • column_name - name of column
  • data_type - column data type
  • max_length - data type max length
  • is_nullable -indicate if column can be null or not
  • default_value -default expression of the column

Rows

  • One row represents one table column
  • Scope of rows: all columns in all tables in a database
  • Ordered by schema, table name, column position

Sample results

You could also get this

Get this interactive HTML data dictionary in minutes with Dataedo.

See live HTML data dictionary sample

Try for free

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