Show tables, describe tables equivalent in redshift

Question

I'm new to aws, can anyone tell me what are redshifts' equivalents to mysql commands?

show tables -- redshift command
describe table_name -- redshift command

score 125 · Answer 1 · edited Apr 15 '21 at 06:46

125

All the information can be found in a PG_TABLE_DEF table, documentation.

Listing all tables in a public schema (default) - show tables equivalent:

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

Description of all the columns from a table called table_name - describe table equivalent:

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

Update:

As pointed by @Kishan Pandey 's answer, if you are looking for details of a schema different by public, you need to set search_path to my_schema. (show search_path display current search path)

Listing tables in my_schema schema:

set search_path to my_schema;
select * from pg_table_def;

edited Apr 15 '21 at 06:46

Rick

7,007
2
49
79

answered Sep 11 '13 at 11:37

Tomasz Tybulewicz

8,487
3
42
44

1

Is there way to fetch default values set of respective columns ? – Bhupender Keswani Dec 29 '14 at 12:59
4

pg_table_def returns index also. safer to use `select distinct table_name from information_schema.columns where table_schema = 'public'` instead. – j_c Jan 27 '15 at 23:19
is that in column order ? – abourget Apr 20 '15 at 21:29
see http://www.alberton.info/postgresql_meta_info.html for a bunch of simpler ways to extract column info, including order – abourget Apr 21 '15 at 14:43
Maybe they used work to at the time of this answer, but these queries don't return all tables anymore (2018-07-11). For example, I created my own schema and created a table in that schema, and the `pg_table_def` table doesn't have any information about that table. The query suggested by @j_c -- to query `information_schema` instead does work. – jbasko Jul 11 '18 at 14:52
1

as pointed by @Kishan Pandey 's answer, if you are looking for details of a schema different by public, you need to `set search_path to my_schema` and consequently update the query to filter on `schemaname = 'my_schema'` – Vzzarr Nov 05 '19 at 13:07
This did not show some tables that `\d+ ` below did.
– Marcus Feb 26 '20 at 18:30
what about temp tables? I want to describe a temp table I created but temp tables cant be created with schema – Eduardo EPF Nov 17 '21 at 09:48

score 89 · Answer 2 · edited Jun 16 '17 at 07:13

89

I had to select from the information schema to get details of my tables and columns; in case it helps anyone:

SELECT * FROM information_schema.tables
WHERE table_schema = 'myschema'; 

SELECT * FROM information_schema.columns
WHERE table_schema = 'myschema' AND table_name = 'mytable';

edited Jun 16 '17 at 07:13

Taylor D. Edmiston

12,088
6
56
76

answered Jan 07 '15 at 16:56

Alex Hinton

1,578
13
15

1

I am storing all our Segment SQL data to AWS Redshift, and so far this is the only solution I have come across that has worked for me! – mgig Mar 28 '16 at 21:53
2

I find this the superior method. The information_schema in Redshift is a superset of pg_table. Furthermore, pg_table_def seems to suffer from some kind of permission issue: while a schema/table will be in pg_tables, the corresponding information in pg_table_def will NOT be there. All I get is pg_catalog and public schemae. Frustrating. – Mark Gerolimatos Aug 08 '17 at 20:08
4

@MarkGerolimatos To see your tables in `pg_table_def`, you must add the schema to your SEARCH_PATH: `SET SEARCH_PATH to '$user', public, YOUR_SCEHMA_NAME`. Definitely not intuitive... – Marco Roy Dec 11 '17 at 19:05
1

@MarcoRoy Worth mentiong that, the `pg_table_def` is more informative as I see from the query results. `pg_table_def` shows whether the column is `distkey` or `sortkey` and its encoding, which are not shown in the `information_schema.columns`. I guess that's where SQL clients like DataGrip gets the DDL from. – Rick Apr 15 '21 at 06:52

Matt · Answer 3 · 2014-11-26T22:51:44.127

33

Or simply:

\dt to show tables

\d+ <table name> to describe a table

Edit: Works using the psql command line client

edited Nov 26 '14 at 22:51

answered Sep 12 '13 at 22:54

Matt

903
1
9
15

This does not work for me .. it would work in Posgres but not Redshift .. better to go with Tomasz' answer. – Mark Butler Nov 24 '14 at 07:43
1

I am accessing Redshift via SQLWorkbench (as recommended in the AWS getting started guide). If I try `\dt` I get `Error: Syntax error at or near "\"`. Are you accessing Redshift in some other way? – Mark Butler Nov 26 '14 at 01:25
2

Yes, using psql command line client. I will update my answer. – Matt Nov 26 '14 at 22:51
2

Actually I didn't realise you could connect to Redshift with the psql client, but it is in the Amazon documentation. It's actually very useful. Thanks. – Mark Butler Dec 03 '14 at 11:22
It shows slightly different information than Tomasz's solution. For example, \d+ tablename does not show sortkey information. – Kirk Dec 30 '14 at 18:06

score 12 · Answer 4 · answered Jun 16 '17 at 10:20

12

Tomasz Tybulewicz answer is good way to go.

SELECT * FROM pg_table_def WHERE tablename = 'YOUR_TABLE_NAME' AND schemaname = 'YOUR_SCHEMA_NAME';

If schema name is not defined in search path , that query will show empty result. Please first check search path by below code.

SHOW SEARCH_PATH

If schema name is not defined in search path , you can reset search path.

SET SEARCH_PATH to '$user', public, YOUR_SCEHMA_NAME

answered Jun 16 '17 at 10:20

Kishan Pandey

191
1
8

1

You may want to add the search path comment to Tomasz's answer. I had this issue the first time I used the pg views and had to research it. – Rob Nov 09 '17 at 16:02
This is the correct solution concerned with `SEARCH_PATH`. But I only need to `set search_path to my_schema_name`. – Rick Apr 15 '21 at 06:36

score 2 · Answer 5 · answered Aug 16 '23 at 02:02

Redshift provides system tables and views which are simpler to use than the postgres default pg_catalog and information_schema tables. You can find the list of tables here

To get list of tables in a schema, you can query SVV_ALL_TABLES / SVV_REDSHIFT_TABLES

    select * from SVV_REDSHIFT_TABLES where schema_name = '<<schema_name>>';

To get table definition including is sort key and distribution key, you can query SVV_REDSHIFT_COLUMNS

    select * from SVV_REDSHIFT_COLUMNS where schema_name = '<<schema_name>>' and table_name = '<<table_name>>';

score 1 · Answer 6 · answered Apr 11 '15 at 08:05

1

You can use - desc / to see the view/table definition in Redshift. I have been using Workbench/J as a SQL client for Redshift and it gives the definition in the Messages tab adjacent to Result tab.

answered Apr 11 '15 at 08:05

Anirudh Singh Rajawat

11
1

1

thanks for that! Still missing a way of listing tables :/ – Madhava Carrillo May 10 '16 at 09:48

Sylvia · Answer 7 · 2017-04-29T16:37:00.070

In the following post, I documented queries to retrieve TABLE and COLUMN comments from Redshift. https://sqlsylvia.wordpress.com/2017/04/29/redshift-comment-views-documenting-data/

Enjoy!

Table Comments

    SELECT n.nspname AS schema_name
     , pg_get_userbyid(c.relowner) AS table_owner
     , c.relname AS table_name
     , CASE WHEN c.relkind = 'v' THEN 'view' ELSE 'table' END 
       AS table_type
     , d.description AS table_description
     FROM pg_class As c
     LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
     LEFT JOIN pg_tablespace t ON t.oid = c.reltablespace
     LEFT JOIN pg_description As d 
          ON (d.objoid = c.oid AND d.objsubid = 0)
     WHERE c.relkind IN('r', 'v') AND d.description > ''
     ORDER BY n.nspname, c.relname ;

Column Comments

    SELECT n.nspname AS schema_name
     , pg_get_userbyid(c.relowner) AS table_owner
     , c.relname AS table_name
     , a.attname AS column_name
     , d.description AS column_description
    FROM pg_class AS c
    INNER JOIN pg_attribute As a ON c.oid = a.attrelid
    INNER JOIN pg_namespace n ON n.oid = c.relnamespace
    LEFT JOIN pg_tablespace t ON t.oid = c.reltablespace
    LEFT JOIN pg_description As d 
     ON (d.objoid = c.oid AND d.objsubid = a.attnum)
    WHERE c.relkind IN('r', 'v')
     AND a.attname NOT         
     IN ('cmax', 'oid', 'cmin', 'deletexid', 'ctid', 'tableoid','xmax', 'xmin', 'insertxid')
    ORDER BY n.nspname, c.relname, a.attname;

score 1 · Answer 8 · answered Feb 28 '20 at 05:37

1

Shortcut

\d for show all tables

\d tablename to describe table

\? for more shortcuts for redshift

answered Feb 28 '20 at 05:37

pankaj yadav E DOT TECH

459
4
5

score 1 · Answer 9 · answered May 13 '21 at 03:32

1

redshift now support show table

show table analytics.dw_users

https://forums.aws.amazon.com/ann.jspa?annID=8641

answered May 13 '21 at 03:32

Gaurav Shah

5,223
7
43
71

score -7 · Answer 10 · edited Apr 06 '17 at 19:04

-7

You can simply use the command below to describe a table.

desc table-name

or

desc schema-name.table-name

edited Apr 06 '17 at 19:04

goto

7,908
10
48
58

answered Apr 06 '17 at 18:36

Andrew Endicott

3
1

Show tables, describe tables equivalent in redshift

10 Answers10

Linked