How to query for a table's primary keys in Redshift

Question

I tried to use the code suggested on the Postgresql wiki (https://wiki.postgresql.org/wiki/Retrieve_primary_key_columns):

SELECT a.attname, format_type(a.atttypid, a.atttypmod) AS data_type
FROM   pg_index i
JOIN   pg_attribute a ON a.attrelid = i.indrelid
                     AND a.attnum = ANY(i.indkey)
WHERE  i.indrelid = 'tablename'::regclass
AND    i.indisprimary;

Unfortunately, it doesn't seem to work in Redshift. And I get this error:

ERROR:  op ANY/ALL (array) requires array on right side

Am I doing something wrong or is this yet another redshift anomaly?

Any help would be greatly appreciated.

Answer 1

Redshift doesn't have the concept of primary keys http://docs.aws.amazon.com/redshift/latest/dg/t_Defining_constraints.html but identity attritube can be used to set uniqueness. (more info at http://docs.aws.amazon.com/redshift/latest/dg/r_CREATE_TABLE_NEW.html)

This is not true.

Redshift does not enforce primary key constraints but they are otherwise available to use. They can be useful when automating data pipelines or data quality checks. They are also recommended by redshift when designing star schemas, because they are used as hints by the query optimizer. https://aws.amazon.com/blogs/big-data/optimizing-for-star-schemas-and-interleaved-sorting-on-amazon-redshift/

Here's one way to get a table's primary key:

SELECT
  schemaname,
  tablename,
  replace(substr(ddl, POSITION('(' IN ddl)+1 ),')','') primary_key
FROM
  admin.v_generate_tbl_ddl
WHERE
  schemaname = 'schema'
  AND tablename='table'
  AND upper(ddl) LIKE '%PRIMARY%';

The code for the view admin.v_generate_tbl_ddl is here: https://github.com/awslabs/amazon-redshift-utils/tree/master/src/AdminViews

Answer 2

You can use the following sql to get the list of primary keys for a table "tablename" in a schema "schemaname"

SELECT
  att.attname
FROM pg_index ind, pg_class cl, pg_attribute att
WHERE 
  cl.oid = 'schemaname."tablename"'::regclass 
  AND ind.indrelid = cl.oid 
  AND att.attrelid = cl.oid
  and att.attnum = ANY(string_to_array(textin(int2vectorout(ind.indkey)), ' '))
  and attnum > 0
  AND ind.indisprimary
order by att.attnum;