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.

Tombolo

what postgres version do you use? Also: what table are you trying to get the index of? – Sulfaguanidine

Answer 1

Redshift doesn't enforce 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)

For details of existing tables, you can use the following query

select column_name, is_nullable, data_type, character_maximum_length 
from information_schema.columns 
where table_schema='schema_name' 
and table_name='table_name' 
order by ordinal_position

Meiosis

re "Redshift doesn't have the concept of primary keys" this is not true; they are just not enforced but the page you link to actually recommends you define them. – Chromaticity

As far as I can tell the above query doesn't actually give constraints on the tables. – Kalfas

Answer 2

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

Radiopaque

This answer seems to assume that 'primary' isn't a substring in any column names. – Kalfas

Answer 3

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;

Ricoriki

Answer 4

The ISO standard information_schema views are sadly not telling the full story on Redshift. I suspect that the constraints are not listed in information_schema.table_constraints because they aren't enforced in Redshift.

HOWEVER there is a way

AWS provides a github repo with lot of admin tools, utilities and views. The views are here

One of those views is v_generate_tbl_ddl

This view can give you the full DDL to recreate tables, including specifying the Primary Key.

I've extracted the relevant part of the view and it will give you the Primary keys. There's other sections of that view that show how to get the dist key, sort key and other useful things:

SELECT 
    c.oid::bigint AS table_id, 
    n.nspname AS schemaname,
    c.relname AS tablename, 
     pg_get_constraintdef(con.oid)::character varying AS PRIMARYKEY /*AS ddl*/
FROM pg_constraint con
JOIN pg_class c ON c.relnamespace = con.connamespace AND c.oid = con.conrelid
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r'::"char" AND pg_get_constraintdef(con.oid) !~~ 'FOREIGN KEY%'::text

Gains

Answer 5

This is so much easier to do from INFORMATION_SCHEMA:

select TC.column_name
from information_schema.table_constraints AS TC
inner join information_schema.key_column_usage AS KCU
  on KCU.constraint_catalogue = TC.constraint_catalogue
  and KCU.constraint_schema = TC.constraint_schema
  and KCU.constraint_name = TC.constraint_name
where TC.constraint_type = 'PRIMARY KEY'
and TC.table_schema = '<my schema>'
and TC.table_name = '<my table>'
order by KCU.ordinal_position

And yes, this works on Redshift.

Armour

Agreed much easier, thanks @Armour Note, I had to change constraint_catalogue to the US spelling constraint_catalog and also column_name was not found in TC so changed it to source from key_column_usage. – Gains

Sadly I found that information_schema.table_constraints does not tell the full story, but I found a reliable method, see https://mcmap.net/q/1401261/-how-to-query-for-a-table-39-s-primary-keys-in-redshift – Gains

Answer 6

dsz's answer didn't work for me, but got really close! (Had to change spelling of "catalogue", select from key_column_usage instead of table_constraints, and add one extra and to the join)

This works for me for redshift and MySQL. Have not explicitly tried Postgres yet, but should work:

select KCU.table_schema, KCU.table_name, KCU.column_name
from information_schema.table_constraints AS TC
inner join information_schema.key_column_usage AS KCU
 on KCU.constraint_catalog = TC.constraint_catalog
 and KCU.constraint_schema = TC.constraint_schema
 and KCU.table_name = TC.table_name
 and KCU.constraint_name = TC.constraint_name
where TC.constraint_type = 'PRIMARY KEY'
and TC.table_schema = '<my schema>'
and TC.table_name = '<my table>'
order by KCU.ordinal_position;

Vaish

Answer 7

information_schema works not for all users

SELECT
    f.attname AS column_name
FROM
    pg_catalog.pg_namespace n
JOIN pg_catalog.pg_class c ON
    n.oid = c.relnamespace
JOIN pg_catalog.pg_attribute f ON
    c.oid = f.attrelid
JOIN pg_catalog.pg_constraint p ON
    p.conrelid = c.oid
    AND f.attnum = ANY (p.conkey)
WHERE
    n.nspname = 'schema_name'
    AND c.relkind = 'r'
    AND c.relname = 'table_name'
    AND p.contype = 'p'
    AND f.attnum > 0
ORDER BY
    f.attnum;

Reprovable

Answer 8

Try with the help of this one: 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

Sulfaguanidine

Answer 9

Harsh's solution worked for me. Posting it here as I still cannot upvote.
IDE: AWS Redshift query editor

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;

Comely