我想在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)。
请尝试下面的查询以深入到所需的索引
查询如下-我亲自尝试过，并经常使用它。
SELECT n.nspname as "Schema",
c.relname as "Name",
CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i'
THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END as "Type",
u.usename as "Owner",
c2.relname as "Table"
FROM pg_catalog.pg_class c
JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid
JOIN pg_catalog.pg_class c2 ON i.indrelid = c2.oid
LEFT JOIN pg_catalog.pg_user u ON u.usesysid = c.relowner
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('i','')
AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
AND pg_catalog.pg_table_is_visible(c.oid)
AND c2.relname like '%agg_transaction%' --table name
AND nspname = 'edjus' -- schema name
ORDER BY 1,2;
# \迪
最简单和最短的方法是\di，它将列出当前数据库中的所有索引。
$ \di
List of relations
Schema | Name | Type | Owner | Table
--------+-----------------------------+-------+----------+---------------
public | part_delivery_index | index | shipper | part_delivery
public | part_delivery_pkey | index | shipper | part_delivery
public | shipment_by_mandator | index | shipper | shipment_info
public | shipment_by_number_and_size | index | shipper | shipment_info
public | shipment_info_pkey | index | shipper | shipment_info
(5 rows)
\di是\d命令的“小兄弟”，该命令将列出当前数据库的所有关系。因此\di当然代表“显示我这个数据库索引”。
输入\diS将列出系统范围内使用的所有索引，这意味着您也将获得所有pg_catalog索引。
$ \diS
List of relations
Schema | Name | Type | Owner | Table
------------+-------------------------------------------+-------+----------+-------------------------
pg_catalog | pg_aggregate_fnoid_index | index | postgres | pg_aggregate
pg_catalog | pg_am_name_index | index | postgres | pg_am
pg_catalog | pg_am_oid_index | index | postgres | pg_am
pg_catalog | pg_amop_fam_strat_index | index | postgres | pg_amop
pg_catalog | pg_amop_oid_index | index | postgres | pg_amop
pg_catalog | pg_amop_opr_fam_index | index | postgres | pg_amop
pg_catalog | pg_amproc_fam_proc_index | index | postgres | pg_amproc
pg_catalog | pg_amproc_oid_index | index | postgres | pg_amproc
pg_catalog | pg_attrdef_adrelid_adnum_index | index | postgres | pg_attrdef
--More--
使用这两个命令，您可以在它之后添加一个+，以获得更多信息，如大小-磁盘空间-索引需要和描述(如果可用)。
$ \di+
List of relations
Schema | Name | Type | Owner | Table | Size | Description
--------+-----------------------------+-------+----------+---------------+-------+-------------
public | part_delivery_index | index | shipper | part_delivery | 16 kB |
public | part_delivery_pkey | index | shipper | part_delivery | 16 kB |
public | shipment_by_mandator | index | shipper | shipment_info | 19 MB |
public | shipment_by_number_and_size | index | shipper | shipment_info | 19 MB |
public | shipment_info_pkey | index | shipper | shipment_info | 53 MB |
(5 rows)
在psql中，你可以很容易地找到关于输入\?
@cope360的精彩回答，转换为使用连接语法。
select t.relname as table_name
, i.relname as index_name
, array_to_string(array_agg(a.attname), ', ') as column_names
from pg_class t
join pg_index ix
on t.oid = ix.indrelid
join pg_class i
on i.oid = ix.indexrelid
join pg_attribute a
on a.attrelid = t.oid
and a.attnum = ANY(ix.indkey)
where t.relkind = 'r'
and t.relname like 'test%'
group by t.relname
, i.relname
order by t.relname
, i.relname
;
我认为这个版本在这个线程上还不存在:它提供了列名列表和索引的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显示了索引中使用的全部列。
