CODEWITHSUNDEEP
postgresqlamazon-redshift
Alex
Alex

Reputation: 488

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

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.

Upvotes: 7

Views: 14562

Answers (9)

tex6
tex6

Reputation: 1

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;

Upvotes: 0

Yauheni Khvainitski
Yauheni Khvainitski

Reputation: 131

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;

Upvotes: 1

Jay Johnson
Jay Johnson

Reputation: 91

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

Upvotes: 9

Davos
Davos

Reputation: 5415

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

Upvotes: 4

Sailendra Pinupolu
Sailendra Pinupolu

Reputation: 1138

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

Upvotes: -1

Nick
Nick

Reputation: 11

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;

Upvotes: 1

dsz
dsz

Reputation: 5202

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.

Upvotes: 3

Harsh Kumar
Harsh Kumar

Reputation: 76

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;

Upvotes: 6

perzsa
perzsa

Reputation: 291

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

Upvotes: 0

Related Questions