Redshift没有主键http://docs.aws.amazon.com/redshift/latest/dg/t_Defining_constraints.html的概念，但是可以使用标识属性来设置唯一性。（更多信息，请访问http://docs.aws.amazon.com/redshift/latest/dg/r_CREATE_TABLE_NEW.html）
这不是真的。
Redshift不强制主键约束，但它们在其他方面也可供使用。它们在自动执行数据管道或数据质量检查时非常有用。在设计星星模式时，Redshift也推荐使用它们，因为它们被查询优化器用作提示。https://aws.amazon.com/blogs/big-data/optimizing-for-star-schemas-and-interleaved-sorting-on-amazon-redshift/的
下面是获取表主键的一种方法：

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

字符串
视图admin.v_generate_tbl_ddl的代码如下所示：https://github.com/awslabs/amazon-redshift-utils/tree/master/src/AdminViews的

您可以使用以下SQL来获取模式“schemaname”中的表“tablename”的主键列表

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;

字符串

遗憾的是，ISO标准information_schema视图并没有讲述Redshift的全部故事。我怀疑这些约束没有在information_schema.table_constraints中列出，因为它们没有在Redshift中强制执行。
但是有一种方法
AWS提供了一个github repo，其中包含许多管理工具，实用程序和视图。视图为here
其中一个视图是v_generate_tbl_ddl
这个视图可以为您提供完整的DDL来重新创建表，包括指定Primary Key。
我已经提取了视图的相关部分，它会给予你主键。该视图的其他部分展示了如何获取dist键，sort键和其他有用的东西：

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

字符串

这在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

字符串
是的，这在红移上有效。

dsz的回答对我不起作用，但真的很接近！（不得不更改“catalogue”的拼写，选择key_column_usage而不是table_constraints，并在连接中添加一个额外的和）
这对我来说适用于redshift和MySQL。尚未明确尝试Postgres，但应该可以工作：

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;

字符串

information_schema不适用于所有用户

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;

字符串

试试这一个的帮助：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

字符串

哈什的解决方案对我很有效。我把它贴在这里，因为我仍然不能投票。
IDE：AWS Redshift查询编辑器

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;

字符串

Redshift不强制主键http://docs.aws.amazon.com/redshift/latest/dg/t_Defining_constraints.html的概念，但身份属性可以用来设置唯一性。（更多信息，请访问http://docs.aws.amazon.com/redshift/latest/dg/r_CREATE_TABLE_NEW.html）
有关现有表的详细信息，可以使用以下查询

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

字符串