In order to list or show all of the tables in a Redshift database, you’ll need to query the PG_TABLE_DEF systems table. An interesting thing to note is the PG_ prefix. This is because Redshift is based off Postgres, so that little prefix is a throwback to REdshift’s Postgres origins.

Running  SELECT * FROM PG_TABLE_DEF will return every column from every table in every schema. This means that systems tables will be included and each table will be listed multiple times, one for each column. For this reason, if you just want to get a list of tables, you’ll want to use the following query:

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

This will return a single column of table names for the public schema, which is probably where most of your data resides.

When we SELECT * FROM PG_TABLE_DEF;, we get the following columns:

  1. schemaname
    1. You most likely want to limit this to “public” otherwise it will also include all of the systems tables.
  2. tablename
    1. The name of the table, yay!
  3. columnname
    1. The name of each column
  4. type
    1. The datatype of each column
  5. encoding
  6. distkey and sortkey
    1. Learn more about Redshift distkey and sortkey.
  7. notnull
    1. Boolean to determine if the column has a NOT NULL constraint.

And there you go! Should be pretty simple. Let us know in the comments if you have any questions!


About Jackson Riso:
Originally from Portland, OR, Jackson wanted to be an archaeologist when he grew up.

FlyData handles real-time replication for all of the major RDS systems, including mySQL, PostgreSQL, Amazon RDS, Amazon Aurora, MariaDB, and Percona.

With an easy one-time setup, you can focus on uncovering data to make business decisions.

No Credit Card Required

By using this website you agree to accept our Privacy Policy and Terms & Conditions