CODEWITHSUNDEEP
postgresqlamazon-redshift
Andrey Belykh
Andrey Belykh

Reputation: 2654

List columns with indexes in Amazon Redshift

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"

Upvotes: 2

Views: 8946

Answers (4)

SQLSylvia
SQLSylvia

Reputation: 51

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;

Upvotes: 4

mike_pdb
mike_pdb

Reputation: 2828

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%'

Upvotes: 1

Andrey Belykh
Andrey Belykh

Reputation: 2654

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

Upvotes: 6

Sandesh Deshmane
Sandesh Deshmane

Reputation: 2305

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.

Upvotes: 0

Related Questions