Find tables with digits in names in Redshift

Tables usually do not have digits in their names, and if they do they have a special meaning. We use them to name backup tables with date of an backup (of single specific table while sensitive operation), archival tables with year or for partitioning tables.

Query below finds all tables with digits in their names.


select table_schema,
from information_schema.tables
where table_name ~ '[0-9]'
      and table_schema not in ('information_schema', 'pg_catalog')
      and table_type = 'BASE TABLE'
order by table_schema,


  • table_schema - name of schema table was found in
  • table_name - name of found table


  • One row represents a table
  • Scope of rows: all found tables
  • Ordered by table schema, 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.
There are no comments. Click here to write the first comment.