postgresql - 列出 Amazon Redshift 中带有索引的列

Question

我需要查询 Redshift 元数据以获取表列的列表，其中包含列是否是主键的一部分的信息。

已有帖子List columns with indexes in PostgreSQL这对 PostgreSQL 有一个答案，但不幸的是，它在 Redshift 上失败并显示“错误:42809:op ANY/ALL(数组)需要右侧的数组”

Answer 1

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