Data TutorialsAmazon Redshift

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.

Visual SQL Hero

Introducing Visual SQL

SQL may be the language of data, but not everyone can understand it. With our visual version of SQL, now anyone at your company can query data from almost any source—no coding required.

Learn about Visual SQL