Get table schema in Redshift

Question

Hello I am trying to retrieve the schema of an existing table. I am mysql developer and am trying to work with amazon redshift. How can I export the schema of an existing table. In mysql we can use the show create table command.

SHOW CREATE TABLE tblName;

Answer 1

Recently I wrote a python script to clone table schemas between redshift clusters. If you only want the columns and column types of a table, you can do it via:

select column_name,
  case
    when data_type = 'integer' then 'integer'
    when data_type = 'bigint' then 'bigint'
    when data_type = 'smallint' then 'smallint'
    when data_type = 'text' then 'text'
    when data_type = 'date' then 'date'
    when data_type = 'real' then 'real'
    when data_type = 'boolean' then 'boolean'
    when data_type = 'double precision' then 'float8'
    when data_type = 'timestamp without time zone' then 'timestamp'
    when data_type = 'character' then 'char('||character_maximum_length||')'
    when data_type = 'character varying' then 'varchar('||character_maximum_length||')'
    when data_type = 'numeric' then 'numeric('||numeric_precision||','||numeric_scale||')'
    else 'unknown'
  end as data_type,
  is_nullable,
  column_default
 from information_schema.columns
 where table_schema = 'xxx' and table_name = 'xxx' order by ordinal_position
;

But if you need the compression types and distkey/sortkeys, you need to query another table:

select * from pg_table_def where tablename = 'xxx' and schemaname='xxx';