我想在PostgreSQL中获得索引上的列。
在MySQL中,您可以使用SHOW INDEXES FOR表并查看Column_name列。
mysql> show indexes from foos;
+-------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| foos | 0 | PRIMARY | 1 | id | A | 19710 | NULL | NULL | | BTREE | |
| foos | 0 | index_foos_on_email | 1 | email | A | 19710 | NULL | NULL | YES | BTREE | |
| foos | 1 | index_foos_on_name | 1 | name | A | 19710 | NULL | NULL | | BTREE | |
+-------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
PostgreSQL中存在类似的东西吗?
我已经在psql命令提示符中尝试了\d(使用-E选项来显示SQL),但它没有显示我正在寻找的信息。
更新:感谢大家的回答。cope360提供了我想要的东西,但也有一些人提供了非常有用的链接。为了将来的参考,请查看pg_index的文档(通过Milen A. Radev)和非常有用的文章从PostgreSQL提取META信息(通过micharov Niklas)。
延伸到@Cope360的好答案。要获取某个表(如果它们是相同的表名但不同的模式),只需使用表OID。
select
t.relname as table_name
,i.relname as index_name
,a.attname as column_name
,a.attrelid tableid
from
pg_class t,
pg_class i,
pg_index ix,
pg_attribute a
where
t.oid = ix.indrelid
and i.oid = ix.indexrelid
and a.attrelid = t.oid
and a.attnum = ANY(ix.indkey)
and t.relkind = 'r'
-- and t.relname like 'tbassettype'
and a.attrelid = '"dbLegal".tbassettype'::regclass
order by
t.relname,
i.relname;
解释:我有表名'tbassettype'在两个模式'dbAsset'和'dbLegal'。要在dbLegal上只获取表,只需让a.attrelid =它的OID。
查询结果:
table | column | type | notnull | index_name | is_index | primarykey | uniquekey | default
-------+----------------+------------------------+---------+--------------+----------+- -----------+-----------+---------
nodes | dns_datacenter | character varying(255) | f | | f | f | f |
nodes | dns_name | character varying(255) | f | dns_name_idx | t | f | f |
nodes | id | uuid | t | nodes_pkey | t | t | t |
(3 rows)
查询:
SELECT
c.relname AS table,
f.attname AS column,
pg_catalog.format_type(f.atttypid,f.atttypmod) AS type,
f.attnotnull AS notnull,
i.relname as index_name,
CASE
WHEN i.oid<>0 THEN 't'
ELSE 'f'
END AS is_index,
CASE
WHEN p.contype = 'p' THEN 't'
ELSE 'f'
END AS primarykey,
CASE
WHEN p.contype = 'u' THEN 't'
WHEN p.contype = 'p' THEN 't'
ELSE 'f'
END AS uniquekey,
CASE
WHEN f.atthasdef = 't' THEN d.adsrc
END AS default FROM pg_attribute f
JOIN pg_class c ON c.oid = f.attrelid
JOIN pg_type t ON t.oid = f.atttypid
LEFT JOIN pg_attrdef d ON d.adrelid = c.oid AND d.adnum = f.attnum
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_constraint p ON p.conrelid = c.oid AND f.attnum = ANY (p.conkey)
LEFT JOIN pg_class AS g ON p.confrelid = g.oid
LEFT JOIN pg_index AS ix ON f.attnum = ANY(ix.indkey) and c.oid = f.attrelid and c.oid = ix.indrelid
LEFT JOIN pg_class AS i ON ix.indexrelid = i.oid
WHERE c.relkind = 'r'::char
AND n.nspname = 'public' -- Replace with Schema name
--AND c.relname = 'nodes' -- Replace with table name, or Comment this for get all tables
AND f.attnum > 0
ORDER BY c.relname,f.attname;
在处理索引时,索引中构造列的顺序与列本身同样重要。
下面的查询以排序的方式列出给定表的所有索引及其所有列。
SELECT
table_name,
index_name,
string_agg(column_name, ',')
FROM (
SELECT
t.relname AS table_name,
i.relname AS index_name,
a.attname AS column_name,
(SELECT i
FROM (SELECT
*,
row_number()
OVER () i
FROM unnest(indkey) WITH ORDINALITY AS a(v)) a
WHERE v = attnum)
FROM
pg_class t,
pg_class i,
pg_index ix,
pg_attribute a
WHERE
t.oid = ix.indrelid
AND i.oid = ix.indexrelid
AND a.attrelid = t.oid
AND a.attnum = ANY (ix.indkey)
AND t.relkind = 'r'
AND t.relname LIKE 'tablename'
ORDER BY table_name, index_name, i
) raw
GROUP BY table_name, index_name
我认为这个版本在这个线程上还不存在:它提供了列名列表和索引的ddl。
CREATE OR REPLACE VIEW V_TABLE_INDEXES AS
SELECT
n.nspname as "schema"
,t.relname as "table"
,c.relname as "index"
,i.indisunique AS "is_unique"
,array_to_string(array_agg(a.attname), ', ') as "columns"
,pg_get_indexdef(i.indexrelid) as "ddl"
FROM pg_catalog.pg_class c
JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid
JOIN pg_catalog.pg_class t ON i.indrelid = t.oid
JOIN pg_attribute a ON a.attrelid = t.oid AND a.attnum = ANY(i.indkey)
WHERE c.relkind = 'i'
and n.nspname not in ('pg_catalog', 'pg_toast')
and pg_catalog.pg_table_is_visible(c.oid)
GROUP BY
n.nspname
,t.relname
,c.relname
,i.indisunique
,i.indexrelid
ORDER BY
n.nspname
,t.relname
,c.relname;
我发现使用函数的索引不链接到列名,所以偶尔你会发现一个索引列表,例如一个列名,而实际上是使用3。
例子:
CREATE INDEX ui1 ON table1 (coalesce(col1,''),coalesce(col2,''),col3)
该查询仅返回'col3'作为索引上的列,但DDL显示了索引中使用的全部列。