在PostgreSQL中列出带索引的列

我想在PostgreSQL中获得索引上的列。

在MySQL中，你可以使用SHOW INDEXES FOR table并查看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的文档(通过米伦·a·拉德夫)和非常有用的文章从PostgreSQL提取META信息(通过MichałNiklas)。

301612 次浏览
小开

原始信息在pg_index中。

小开

只需执行:\d table_name

但是我不确定你说没有列的信息是什么意思。

例如:

# \d pg_class
Table "pg_catalog.pg_class"
Column      |   Type    | Modifiers
-----------------+-----------+-----------
relname         | name      | not null
relnamespace    | oid       | not null
reltype         | oid       | not null
reloftype       | oid       | not null
relowner        | oid       | not null
relam           | oid       | not null
relfilenode     | oid       | not null
reltablespace   | oid       | not null
relpages        | integer   | not null
reltuples       | real      | not null
reltoastrelid   | oid       | not null
reltoastidxid   | oid       | not null
relhasindex     | boolean   | not null
relisshared     | boolean   | not null
relistemp       | boolean   | not null
relkind         | "char"    | not null
relnatts        | smallint  | not null
relchecks       | smallint  | not null
relhasoids      | boolean   | not null
relhaspkey      | boolean   | not null
relhasexclusion | boolean   | not null
relhasrules     | boolean   | not null
relhastriggers  | boolean   | not null
relhassubclass  | boolean   | not null
relfrozenxid    | xid       | not null
relacl          | aclitem[] |
reloptions      | text[]    |
Indexes:
"pg_class_oid_index" UNIQUE, btree (oid)
"pg_class_relname_nsp_index" UNIQUE, btree (relname, relnamespace)

它清楚地显示给定索引在该表上的列。

小开

\d tablename显示了8.3.8版本中我的列名。

 "username_idx" UNIQUE, btree (username), tablespace "alldata1"
小开

\d table_name显示了来自psql的这些信息，但如果你想使用SQL从数据库中获取这些信息，那么可以查看从PostgreSQL提取META信息。

我在我的实用程序从db模式报告一些信息中使用这些信息来比较测试和生产环境中的PostgreSQL数据库。

小开

一些样本数据…

create table test (a int, b int, c int, constraint pk_test primary key(a, b));
create table test2 (a int, b int, c int, constraint uk_test2 unique (b, c));
create table test3 (a int, b int, c int, constraint uk_test3b unique (b), constraint uk_test3c unique (c), constraint uk_test3ab unique (a, b));

使用pg_get_indexdef函数:

select pg_get_indexdef(indexrelid) from pg_index where indrelid = 'test'::regclass;


pg_get_indexdef
--------------------------------------------------------
CREATE UNIQUE INDEX pk_test ON test USING btree (a, b)
(1 row)




select pg_get_indexdef(indexrelid) from pg_index where indrelid = 'test2'::regclass;
pg_get_indexdef
----------------------------------------------------------
CREATE UNIQUE INDEX uk_test2 ON test2 USING btree (b, c)
(1 row)




select pg_get_indexdef(indexrelid) from pg_index where indrelid ='test3'::regclass;
pg_get_indexdef
------------------------------------------------------------
CREATE UNIQUE INDEX uk_test3b ON test3 USING btree (b)
CREATE UNIQUE INDEX uk_test3c ON test3 USING btree (c)
CREATE UNIQUE INDEX uk_test3ab ON test3 USING btree (a, b)
(3 rows)
小开
最佳答案

创建一些测试数据…

create table test (a int, b int, c int, constraint pk_test primary key(a, b));
create table test2 (a int, b int, c int, constraint uk_test2 unique (b, c));
create table test3 (a int, b int, c int, constraint uk_test3b unique (b), constraint uk_test3c unique (c),constraint uk_test3ab unique (a, b));

列出索引的索引和列:

select
t.relname as table_name,
i.relname as index_name,
a.attname as column_name
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 'test%'
order by
t.relname,
i.relname;


table_name | index_name | column_name
------------+------------+-------------
test       | pk_test    | a
test       | pk_test    | b
test2      | uk_test2   | b
test2      | uk_test2   | c
test3      | uk_test3ab | a
test3      | uk_test3ab | b
test3      | uk_test3b  | b
test3      | uk_test3c  | c

卷起列名:

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,
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 'test%'
group by
t.relname,
i.relname
order by
t.relname,
i.relname;


table_name | index_name | column_names
------------+------------+--------------
test       | pk_test    | a, b
test2      | uk_test2   | b, c
test3      | uk_test3ab | a, b
test3      | uk_test3b  | b
test3      | uk_test3c  | c
小开

结合其他代码并创建一个视图:

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;
小开

如果你想保持索引中的列顺序，这里有一个(非常丑陋的)方法:

select table_name,
index_name,
array_agg(column_name)
from (
select
t.relname as table_name,
i.relname as index_name,
a.attname as column_name,
unnest(ix.indkey) as unn,
a.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.relnamespace = <oid of the schema you're interested in>
order by
t.relname,
i.relname,
generate_subscripts(ix.indkey,1)) sb
where unn = attnum
group by table_name, index_name

列顺序存储在pg_index中。indkey列，我是根据这个数组的下标排序的。

小开

查询结果:

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
t.relname table_name,
ix.relname index_name,
indisunique,
indisprimary,
regexp_replace(pg_get_indexdef(indexrelid), '.*\((.*)\)', '\1') columns
FROM pg_index i
JOIN pg_class t ON t.oid = i.indrelid
JOIN pg_class ix ON ix.oid = i.indexrelid
WHERE t.relname LIKE 'test%'

｀

小开

该命令还显示了表变量、索引和约束的视图

=# \d table_name;

例子:

testannie=# \d dv.l_customer_account;
小开

在处理索引时，索引中构造列的顺序与列本身同样重要。

下面的查询以排序的方式列出给定表的所有索引及其所有列。

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
小开

请尝试下面的查询以深入到所需的索引

查询如下-我亲自尝试过，并经常使用它。

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;
小开

类似于接受的答案，但是在pg_attribute上使用左连接作为正常的连接或使用pg_attribute查询不会给出类似于
的索引 create unique index unique_user_name_index on users (lower(name)) < / p > 

select
row_number() over (order by c.relname),
c.relname as index,
t.relname as table,
array_to_string(array_agg(a.attname), ', ') as column_names
from pg_class c
join pg_index i on c.oid = i.indexrelid and c.relkind='i' and c.relname not like 'pg_%'
join pg_class t on t.oid = i.indrelid
left join pg_attribute a on a.attrelid = t.oid and a.attnum = ANY(i.indkey)
group by t.relname, c.relname order by c.relname;
小开

@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
;
小开

下面是一个函数，它包装了cope360的答案:

CREATE OR REPLACE FUNCTION getIndices(_table_name varchar)
RETURNS TABLE(table_name varchar, index_name varchar, column_name varchar) AS $$
BEGIN
RETURN QUERY
select
t.relname::varchar as table_name,
i.relname::varchar as index_name,
a.attname::varchar as column_name
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 = _table_name
order by
t.relname,
i.relname;
END;
$$ LANGUAGE plpgsql;

用法:

select * from getIndices('<my_table>')
小开

我认为这个版本在这个线程上还不存在:它提供了列名列表和索引的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显示了索引中使用的全部列。

小开

PostgreSQL (pg_indexes):

SELECT * FROM pg_indexes WHERE tablename = 'mytable';

MySQL (SHOW INDEX):

SHOW INDEX FROM mytable;
小开

# \di

最简单和最短的方法是\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命令，该命令将列出当前__abc3atababdb的所有关系。因此，\di当然代表“show me this databases 我ndexes";

输入\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。

小开

稍微修改一下@cope360的回答:

create table test (a int, b int, c int, constraint pk_test primary key(c, a, b));
select i.relname as index_name,
ix.indisunique as is_unique,
a.attname as column_name,
from pg_class c
inner join pg_index ix on c.oid=ix.indrelid
inner join pg_class i on ix.indexrelid=i.oid
inner join pg_attribute a on a.attrelid=c.oid and a.attnum=any(ix.indkey)
where c.oid='public.test'::regclass::oid
order by array_position(ix.indkey, a.attnum) asc;

这将显示索引列的正确顺序:

index_name      is_unique  column_name
pk_test         true       c
pk_test         true       a
pk_test         true       b
小开 
select t.relname as table_name,
i.relname as index_name,
array_position(ix.indkey,a.attnum) pos,
a.attname as column_name
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 'orders'
order by t.relname, i.relname, array_position(ix.indkey,a.attnum)
小开

@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 INDEX语句，这样你就可以删除它们，然后作为管理过程的一部分重新创建(这是我的情况)。在这种情况下，你可以使用pg_dump只转储post-data部分，然后再从中转储grep 'CREATE INDEX'。

PGPASSWORD=<pwd> pg_dump --host <host> --port <port> -U <user> -d <database> --section=post-data > post-data.sql
grep 'CREATE INDEX' postdata.sql > create_index.sql
这可能是有帮助的，因为一些索引可能很难重新创建，例如那些使用特定的opclass(例如gin_trgm_ops)或其他一些 我通常很难重建。


提交答案