Justin Kelly various ramblings

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 c.oid = con.conrelid(+)

Here is an example of the output.

RedShift DDL SQL

Justin Kelly

Justin Kelly

Web Developer | Business Analytics | Library Technology based in Melbourne, Australia

Contact me Follow on Twitter


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