How do I get table and columns information from Redshift?

Question

pg_tables provides a list of tables. Is there a pg_columns or its equivalent to provide the list of columns?

In DB2, I would query sysibm.systables/columns to get such information. What is the equivalent in redshift?

Answer 1

Use PG_TABLE_DEF table for getting that information:

It looks like this:

select * from pg_table_def where tablename = 't2';

schemaname|tablename|column|  type   | encoding | distkey |sortkey| notnull 
----------+---------+------+---------+----------+---------+-------+---------
public    | t2      | c1   | bigint  | none     | t       |     0 | f
public    | t2      | c2   | integer | mostly16 | f       |     0 | f
public    | t2      | c3   | integer | none     | f       |     1 | t
public    | t2      | c4   | integer | none     | f       |     2 | f
(4 rows)