Friday, January 16, 2015

Amazon Redshift: Show Table DDL SQL script

Redshift as a database is still evolving and every now and then we face issues which is a piece of cake in other mature databases. For example, if you want to get all columns and their data-types in Oracle you would simply write "Describe "

But there is no such thing in Redshift as of now. To circumvent that I wrote following SQL script, which helps in generating table DDL.
Just replace the < TABLE >  and < SCHEMA>  with your table name and Schema


  1. you can use psql>\d mytable from the command line


    select * from pg_table_def where tablename = 'mytable'

  2. just an fyi, when i connect to redshift using psql,
    and set my search_path, the \d or \d+ does not show the distribution key. Please let me know the specifics of how you accomplished this, what version of the postgres/greenplum client are you using?

  3. You might be qualified to receive a $1,000 Amazon Gift Card.