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

Data Engineeer, Business Analytics, Web Developer, Library Technology specialising in PHP and Tableau

Based in Melbourne, Australia

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