我想在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接受的答案很好，但我想要一些更像Oracle的DBA_IND_COLUMNS, ALL_IND_COLUMNS和USER_IND_COLUMNS(例如，报告表/索引模式和索引在多列索引中的位置)，所以我把接受的答案改编成这样:
with
ind_cols as (
select
n.nspname as schema_name,
t.relname as table_name,
i.relname as index_name,
a.attname as column_name,
1 + array_position(ix.indkey, a.attnum) as column_position
from
pg_catalog.pg_class t
join pg_catalog.pg_attribute a on t.oid = a.attrelid
join pg_catalog.pg_index ix on t.oid = ix.indrelid
join pg_catalog.pg_class i on a.attnum = any(ix.indkey)
and i.oid = ix.indexrelid
join pg_catalog.pg_namespace n on n.oid = t.relnamespace
where t.relkind = 'r'
order by
t.relname,
i.relname,
array_position(ix.indkey, a.attnum)
)
select *
from ind_cols
where schema_name = 'test'
and table_name = 'indextest'
order by schema_name, table_name
;
这将给出如下输出:
schema_name | table_name | index_name | column_name | column_position
-------------+------------+------------+-------------+-----------------
test | indextest | testind1 | singleindex | 1
test | indextest | testind2 | firstoftwo | 1
test | indextest | testind2 | secondoftwo | 2
(3 rows)
结合其他代码并创建一个视图:
CREATE OR REPLACE VIEW view_index AS
SELECT
n.nspname as "schema"
,t.relname as "table"
,c.relname as "index"
,pg_get_indexdef(indexrelid) as "def"
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
WHERE c.relkind = 'i'
and n.nspname not in ('pg_catalog', 'pg_toast')
and pg_catalog.pg_table_is_visible(c.oid)
ORDER BY
n.nspname
,t.relname
,c.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显示了索引中使用的全部列。
# \迪
最简单和最短的方法是\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的好答案。要获取某个表(如果它们是相同的表名但不同的模式)，只需使用表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。
