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 trueTo 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 trueThis 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
venueThere we have it! A simple yet effective method for retrieving table information from within your Amazon Redshift cluster.