updated and cleaner version of awslabs/amazon-redshift-utils src/AdminViews/v_generate_tbl_ddl.sql

get_table_definition.sql

/* This is designed to be run in Python, with the {} vars below templated in. */
WITH vars AS (
  SELECT
    '{schema}' AS schemaname,
    '{table}' AS tablename  
),

base_info AS (
  SELECT
    cls.oid                                                       AS table_id, 
    nsp.nspname                                                   AS schemaname, 
    cls.relname                                                   AS tablename,
    quote_ident(nsp.nspname) || '.' || quote_ident(cls.relname)   AS qualified_name,
    atr.attnum                                                    AS attnum,
    quote_ident(atr.attname) || ' '                               AS col_name,
    upper( /* transforming to uppercase and short names */     /* AS col_datatype */
      replace(
        replace(
          replace(
            replace(
              replace(
                replace(
                  format_type(atr.atttypid, atr.atttypmod), 'character varying', 'varchar'
                ), 'text', 'varchar'
              ), 'character', 'char'
            ), 'double precision', 'float'
          ), 'timestamp with time zone', 'timestamptz'
        ), 'timestamp without time zone', 'timestamp'
      ) ) || ' '                                                  AS col_datatype,
    'ENCODE ' || format_encoding(atr.attencodingtype) || ' '      AS col_encoding,
    CASE 
      WHEN atr.atthasdef IS TRUE 
        THEN 'DEFAULT ' || def.adsrc || ' '
      ELSE ''
    END                                                           AS col_default,
    CASE
      WHEN atr.attnotnull IS TRUE
        THEN 'NOT '
      ELSE ''
    END || 'NULL '                                                AS col_nullable,
    cls.reldiststyle                                              AS diststyle,
    atr.attisdistkey                                              AS col_distkey,
    atr.attsortkeyord                                             AS col_sortkey
  FROM pg_namespace                                                 AS nsp
  JOIN pg_class                                                     AS cls
    ON nsp.oid = cls.relnamespace
  JOIN pg_attribute                                                 AS atr
    ON cls.oid = atr.attrelid
  LEFT JOIN pg_attrdef                                              AS def
    ON  atr.attrelid = def.adrelid 
    AND atr.attnum = def.adnum
  JOIN pg_constraint                                                AS con
    ON  cls.relnamespace = con.connamespace
    AND cls.oid = con.conrelid
  WHERE 1=1
    AND cls.relkind = 'r'
    AND atr.attnum > 0
    AND nsp.nspname = (SELECT schemaname FROM vars)
    AND cls.relname = (SELECT tablename FROM vars)
  ORDER BY atr.attnum
),

constraint_info AS (
  SELECT
    cls.oid                                                       AS table_id, 
    nsp.nspname                                                   AS schemaname, 
    cls.relname                                                   AS tablename,
    quote_ident(nsp.nspname) || '.' || quote_ident(cls.relname)   AS qualified_name,
    con.oid                                                       AS con_oid
  FROM pg_namespace                                                 AS nsp
  JOIN pg_class                                                     AS cls
    ON nsp.oid = cls.relnamespace
  JOIN pg_constraint                                                AS con
    ON  cls.relnamespace = con.connamespace
    AND cls.oid = con.conrelid
  WHERE 1=1
    AND nsp.nspname = (SELECT schemaname FROM vars)
    AND cls.relname = (SELECT tablename FROM vars)
),

seq_01_command AS (
  SELECT
    table_id                                          AS table_id,
    qualified_name                                    AS qualified_name,
    1                                                 AS seq_id,
    'CREATE TABLE ' || qualified_name || ' ('         AS ddl
  FROM base_info
  LIMIT 1
),

seq_02_columns AS (
  SELECT
    table_id                                          AS table_id,
    qualified_name                                    AS qualified_name,
    100000000 + attnum                                AS seq_id,
    ( /* build the column ddl */                   /* AS ddl */
      '  '             /* two spaces */
      || col_name      /* (quoted) column name */
      || col_datatype  /* uppercase and short names data type */
      || col_nullable  /* nullable or not */
      || col_encoding  /* column encoding */
      || col_default   /* default value */
      || ',' )                                        AS ddl
  FROM base_info
),

seq_03_constraints AS (
  SELECT
    table_id                                          AS table_id,
    qualified_name                                    AS qualified_name,
    200000000 + con_oid                               AS seq_id,
    ( /* build the constraint ddl */               /* AS ddl */
      '  '             /* two spaces */
      || pg_get_constraintdef(con_oid) )              AS ddl
  FROM constraint_info
  WHERE 1=1
    AND pg_get_constraintdef(con_oid) !~~ 'FOREIGN KEY%'
),

seq_04_seperator AS (
  SELECT
    table_id                                          AS table_id,
    qualified_name                                    AS qualified_name,
    299999999                                         AS seq_id,
    ')'                                               AS ddl
  FROM base_info
  LIMIT 1
),

seq_05_diststyle AS (
  SELECT
    table_id                                          AS table_id,
    qualified_name                                    AS qualified_name,
    300000001                                         AS seq_id,
    'DISTSTYLE ' || CASE diststyle
      WHEN 0 THEN 'EVEN'
      WHEN 1 THEN 'KEY'
      WHEN 8 THEN 'ALL'
      WHEN 9 THEN 'AUTO'
      ELSE '<<Error - UNKNOWN>>'
      END                                             AS ddl
  FROM base_info
  LIMIT 1
),

seq_06_distkey AS (
  SELECT
    table_id                                          AS table_id,
    qualified_name                                    AS qualified_name,
    400000001                                         AS seq_id,
    'DISTKEY (' || quote_ident(col_name) || ')'       AS ddl
  FROM base_info
  WHERE col_distkey IS TRUE
),

seq_07_sortkey_open AS (
  SELECT
    table_id                                          AS table_id,
    qualified_name                                    AS qualified_name,
    499999999                                         AS seq_id,
    'SORTKEY ('                                      AS ddl
  FROM base_info
  WHERE col_sortkey > 0
),

seq_08_sortkey_list AS (
  SELECT
    table_id                                          AS table_id,
    qualified_name                                    AS qualified_name,
    500000000 + col_sortkey                           AS seq_id,
    '  ' || quote_ident( col_name )                   AS ddl
  FROM base_info
  WHERE col_sortkey > 0
),

seq_09_sortkey_close AS (
  SELECT
    table_id                                          AS table_id,
    qualified_name                                    AS qualified_name,
    599999999                                         AS seq_id,
    ' )'                                              AS ddl
  FROM base_info
  WHERE col_sortkey > 0
),

seq_10_final AS (
  SELECT
    table_id                                          AS table_id,
    qualified_name                                    AS qualified_name,
    999999999                                         AS seq_id,
    ';'                                               AS ddl
  FROM base_info
  LIMIT 1
)

(SELECT qualified_name, ddl FROM seq_01_command ORDER BY seq_id)
UNION ALL
(SELECT qualified_name, ddl FROM seq_02_columns ORDER BY seq_id)
UNION ALL
(SELECT qualified_name, ddl FROM seq_03_constraints ORDER BY seq_id)
UNION ALL
(SELECT qualified_name, ddl FROM seq_04_seperator ORDER BY seq_id)
UNION ALL
(SELECT qualified_name, ddl FROM seq_05_diststyle ORDER BY seq_id)
UNION ALL
(SELECT qualified_name, ddl FROM seq_06_distkey ORDER BY seq_id)
UNION ALL
(SELECT qualified_name, ddl FROM seq_07_sortkey_open ORDER BY seq_id)
UNION ALL
(SELECT qualified_name, ddl FROM seq_08_sortkey_list ORDER BY seq_id)
UNION ALL
(SELECT qualified_name, ddl FROM seq_09_sortkey_close ORDER BY seq_id)
UNION ALL
(SELECT qualified_name, ddl FROM seq_10_final ORDER BY seq_id);