AWS RedShift DDL SQL script

If you are using AWS RedShift and a sql client that doesn’t auto show table details/description you can use the below script and output the full metdata for a table

Just replace INSERT_TABLENAME_HERE and INSERT_SCHEMA_NAME_HERE with the scheme and table name

SELECT DISTINCT n.nspname AS schemaname
 ,c.relname AS tablename
 ,a.attname AS COLUMN
 ,a.attnum AS column_position
 ,pg_catalog.format_type(a.atttypid, a.atttypmod) AS TYPE
 ,pg_catalog.format_encoding(a.attencodingtype) AS encoding
  ,a.attisdistkey AS distkey
 ,a.attsortkeyord AS sortkey
 ,a.attnotnull AS notnull
 ,a.attencodingtype AS compression
 ,con.conkey AS primary_key_column_ids
 ,con.contype AS con_type
FROM pg_catalog.pg_namespace n
 ,pg_catalog.pg_class c
 ,pg_catalog.pg_attribute a
 ,pg_constraint con
 ,pg_catalog.pg_stats stats
WHERE n.oid = c.relnamespace
 AND c.oid = a.attrelid
 AND a.attnum > 0
 AND c.relname NOT LIKE '%pkey'
 AND lower(c.relname) = 'INSERT_TABLENAME_HERE'
 AND n.nspname = 'INSERT_SCHEME_NAME_HERE'
 AND c.oid = con.conrelid(+)
ORDER BY A.ATTNUM
;

Here is an example of the output.

RedShift DDL SQL

Justin Kelly

Justin Kelly

Web Developer, Business Analytics, Data Engineer specialising in PHP, ReactJS, Tableau, AWS RedShift and Matillion ETL

Based in Melbourne, Australia

Feel free to contact me justin@kelly.org.au or _justin_kelly

Comments

Joe Harris

You can also use v_generate_tbl_ddl from our Admin Views to generate full DDL (with DIST and SORT keys and column encodings) for Redshift tables: https://github.com/awslabs/amazon-redshift-utils/tree/master/src/AdminViews

Justin Kelly

Thanks Joe!! didn’t know about these - look great - thanks for posting!!

Leave a comment

Name Notify me of replies by email.