Close

How to Show, List or Describe Tables in Amazon Redshift

Posted by: AJ Welch

Amazon Redshift retains a great deal of metadata about the various databases within a cluster and finding a list of tables is no exception to this rule.

The most useful object for this task is the PG_TABLE_DEF table, which as the name implies, contains table definition information. Note: The PG_ prefix is just a holdover from PostgreSQL, the database technology from which Amazon Redshift was developed.

To begin finding information about the tables in the system, you can simply return columns from PG_TABLE_DEF:

SELECT
  *
FROM
  PG_TABLE_DEF;

For better or worse, PG_TABLE_DEF contains information about everything in the system, so the results of such an open query will be massive, but should give you an idea of what PG_TABLE_DEF is capable of:

schemaname  tablename                   column          type            encoding distkey sortkey notnull
--------------------------------------------------------------------------------------------------------
pg_catalog  padb_config_harvest         name            character(136)  none     false   0       true
pg_catalog  padb_config_harvest         harvest         integer         none     false   0       true
pg_catalog  padb_config_harvest         archive         integer         none     false   0       true
pg_catalog  padb_config_harvest         directory       character(500)  none     false   0       true
pg_catalog  pg_aggregate                aggfnoid        regproc         none     false   0       true
pg_catalog  pg_aggregate                aggtransfn      regproc         none     false   0       true
pg_catalog  pg_aggregate                aggfinalfn      regproc         none     false   0       true
pg_catalog  pg_aggregate                aggtranstype    oid             none     false   0       true
pg_catalog  pg_aggregate                agginitval      text            none     false   0       false
pg_catalog  pg_aggregate_fnoid_index    aggfnoid        regproc         none     false   0       false
pg_catalog  pg_am                       amname          name            none     false   0       true
pg_catalog  pg_am                       amowner         integer         none     false   0       true
pg_catalog  pg_am                       amstrategies    smallint        none     false   0       true

To limit the results to user-defined tables, it’s important to specify the schemaname column to return only results which are public:

SELECT
  *
FROM
  PG_TABLE_DEF
WHERE
  schemaname = 'public';
schemaname  tablename   column   type                   encoding    distkey sortkey notnull
-------------------------------------------------------------------------------------------
public      category    catid    smallint               none        true    1       true
public      category    catgroup character varying(10)  none        false   0       false
public      category    catname  character varying(10)  none        false   0       false
public      category    catdesc  character varying(50)  none        false   0       false
public      date        dateid   smallint               none        true    1       true
public      date        caldate  date                   none        false   0       true
public      event       eventid  integer                none        true    0       true
public      event       venueid  smallint               none        false   0       true
public      event       catid    smallint               none        false   0       true

This shows us all the columns (and their associated tables) that exist and that are public (and therefore user-created).

Lastly, if we are solely interested only the names of tables which are user-defined, we’ll need to filter the above results by retrieving DISTINCT items from within the tablename column:

SELECT
  DISTINCT tablename
FROM
  PG_TABLE_DEF
WHERE
  schemaname = 'public';

This returns only the unique public tables within the system:

tablename
---------
category
date
event
listing
sales
users
venue

There we have it! A simple yet effective method for retrieving table information from within your Amazon Redshift cluster.