Find all numeric columns in PostgreSQL database

Numeric in PostgreSQL are columns with the following data types: smallint, integer, bigint, decimal, numeric, real, double precision, smallserial, serial, bigserial, money. The query below lists all columns with numeric data types.

Query

select col.table_schema,
       col.table_name,
       col.ordinal_position as col_id,
       col.column_name,
       col.data_type,
       col.numeric_precision,
       col.numeric_scale
from information_schema.columns col
join information_schema.tables tab on tab.table_schema = col.table_schema
                                   and tab.table_name = col.table_name
                                   and tab.table_type = 'BASE TABLE'
where col.data_type in ('smallint', 'integer', 'bigint', 
                        'decimal', 'numeric', 'real', 'double precision',
                        'smallserial', 'serial', 'bigserial', 'money')
      and col.table_schema not in ('information_schema', 'pg_catalog')
order by col.table_schema,
         col.table_name,
         col.ordinal_position;

Columns

  • table_schema - name of the schema
  • table_name - name of the table
  • col_id - column position in table
  • column_name - name of the column
  • data_type - type of data
  • numeric_precision - precision of the column
  • numeric_scale - scale of column

Rows

  • One row represents one column with a numeric data type
  • Scope of rows: all columns containing numeric data types in the schema
  • Ordered by database name and table name

Sample results

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