2

I need to query Redshift metadata to get a list of table columns that includes information whether the column is part of primary key or not.

There is a post already List columns with indexes in PostgreSQL that has an answer for PostgreSQL, however unfortunately, it fails on Redshift with "ERROR: 42809: op ANY/ALL (array) requires array on right side"

Community
  • 1
  • 1
Andrey Belykh
  • 2,578
  • 4
  • 32
  • 46
  • Query like this can be probably used as a workaround if I assume that there are no more that 3 columns in a primary key SELECT attname column_name, attnotnull, format_type(atttypid, atttypmod) as column_type, atttypmod, i.indisprimary as primary_key, col_description(attrelid, attnum) as description FROM pg_attribute c LEFT OUTER JOIN pg_index i ON c.attrelid = i.indrelid AND (c.attnum = i.indkey[0] OR c.attnum = i.indkey[1] OR c.attnum = i.indkey[2]) AND i.indisprimary where c.attnum > 0 AND NOT c.attisdropped AND c.attrelid = 100059 order by attnum – Andrey Belykh Apr 29 '15 at 16:12

4 Answers4

6

I figured out how to do it with the help of this https://bitbucket.org/zzzeek/sqlalchemy/pull-request/6/sqlalchemy-to-support-postgresql-80/diff

SELECT attname column_name, attnotnull, 
  format_type(atttypid, atttypmod) as column_type, atttypmod,
  i.indisprimary as primary_key,
  col_description(attrelid, attnum) as description
FROM pg_attribute c
  LEFT OUTER JOIN pg_index i
  ON c.attrelid = i.indrelid AND i.indisprimary AND
  c.attnum = ANY(string_to_array(textin(int2vectorout(i.indkey)), ' '))
where c.attnum > 0 AND NOT c.attisdropped AND c.attrelid = :tableOid
order by attnum
Andrey Belykh
  • 2,578
  • 4
  • 32
  • 46
  • 3
    Seriously? What is this stuff? Might as well take a binary dump of the database and reverse engineer it :-) Is this REALLY what is required to get this information? Is there really no sort of "show create tables" like there is in MySQL (for example) – Mark Gerolimatos Aug 18 '16 at 09:37
4

The following worked for me:

    SELECT   n.nspname as schema_name,
       t.relname as table_name,
       i.relname as index_name,
       c.contype as index_type,
       a.attname as column_name,
       a.attnum AS column_position
    FROM  pg_class t
    INNER JOIN  pg_index      AS ix ON t.oid = ix.indrelid
    INNER JOIN  pg_constraint AS c  ON ix.indrelid = c.conrelid
    INNER JOIN  pg_class      AS i  ON i.oid = ix.indexrelid
    INNER JOIN  pg_attribute  AS a  ON a.attrelid = t.oid
    AND a.attnum= ANY(string_to_array(textin(int2vectorout(ix.indkey)),' ')::int[])
  INNER JOIN  pg_namespace  AS n  ON n.oid = t.relnamespace;
SQLSylvia
  • 51
  • 1
  • 4
1

You can leverage the table DDL view AWS published a few months ago (https://github.com/awslabs/amazon-redshift-utils/blob/master/src/AdminViews/v_generate_tbl_ddl.sql) by picking out the constraint component and parsing out the key columns:

select schemaname,tablename, substring(ddl,charindex('(',ddl)+1, charindex(')',ddl)-1-charindex('(',ddl)) 
from 
(
SELECT 
    n.nspname AS schemaname
   ,c.relname AS tablename
   ,200000000 + CAST(con.oid AS INT) AS seq
   ,'\t,' + pg_get_constraintdef(con.oid) AS ddl
  FROM
  pg_constraint AS con
  INNER JOIN pg_class AS c
    ON c.relnamespace = con.connamespace
    AND c.relfilenode = con.conrelid
  INNER JOIN pg_namespace AS n
    ON n.oid = c.relnamespace 
  WHERE c.relkind = 'r'
  ORDER BY seq
)

Note that this query also gives you foreign key columns. It's easy enough to filter those out by appending the query with 

   where ddl like '%PRIMARY KEY%'
mike_pdb
  • 2,828
  • 16
  • 16
0

Use below query:

select * from pg_table_def where tablename = 'mytablename'

This will give you all columns for table along with their data type , encoding and if it has sort key or dist key.

Sandesh Deshmane
  • 2,247
  • 1
  • 22
  • 25