Redshift Show and Describe Table Command Alternative

When you work on relatively big Enterprise data warehouse (EDW), you will have a large number of tables with different structures. The table structure includes, column name, type of data type, distribution style or sort key use. Redshift does not provide show (list) or describe SQL command. Though, you can use psql command line options. In this article, we will check what are Redshift show and describe table command alternative with an examples.

Redshift Show and Describe Table Command Alternative

As mentioned earlier, the Redshift SQL reference does not provide SHOW or DESCRIBE (DESC) SQL commands. You have to find alternative methods that allow you to list tables present in the database and provide the structure of a tables.

Redshift SHOW Tables Command Alternative

Many databases, Hive support SHOW TABLES commands to list all the tables available in the connected database or schema. Unfortunately, Redshift does not provide SHOW TABLES command. It has SHOW command, but it does not list tables.

As an alternative method, you can query a PG_TABLE_DEF system table that returns information about tables that are visible to the user.

For example, consider below query to list all tables present in the public schema.

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

You might want to save this query somewhere in your frequently used queries and use it when you want to list all tables in a given schema.

Redshift DESCRIBE Table Command Alternative

As the name suggests, DESCRIBE is used to describe command to describe the structure of a table. many databases such as MySQL, Hive provides DESCRIBE command that allows you to get the structure of a given table.

For example, consider below example of Hive describe command.

describe dup_demo;
+-----------+------------+----------+--+
| col_name  | data_type  | comment  |
+-----------+------------+----------+--+
| id        | int        |          |
| name      | string     |          |
+-----------+------------+----------+--+
2 rows selected (0.244 seconds)

As an alternative, you can query a PG_TABLE_DEF system table that returns information about tables that are visible to the user.

For example, consider below query to provide structure of a given table.

SELECT *
FROM pg_table_def
WHERE tablename = 'table_name'
AND schemaname = 'public';

Redshift PSQL Command Line Options to List and Describe Table

PostgreSQL does provide a command line option to list all the tables in connected schema and describe the structure of a given table.

Redshift SHOW TABLES using PostgreSQL psql

From the psql command line interface,

First, set search path to schema from that you want to list tables.

 set search_path to test_schema;

Then, ‘dt’ command shows all tables in the current schema

\dt

Redshift DESCRIBE table structure using PostgreSQL psql

From the psql command line interface,

First, set search path to schema from that you want to list tables.

 set search_path to test_schema;

Second, issue the command \d table_name or \d+ table_name to find the information on columns of a table.

\d test_table

For more information on connecting Redshift to psql, read my other post: Steps to Connect to Redshift cluster using PostgreSQL – psql

Hope this helps 🙂