我根据@Erwin Brandstetter 的答案提出了以下查询来获取表名称、index_type、operator_classes 和索引的唯一性:
SELECT i.indrelid::regclass::text AS table
, c.relname AS index_name
, a.amname AS index_type
, opc.operator_classes
, i.indisunique AS is_unique
, array_agg(att.attname) as column_names
FROM pg_catalog.pg_namespace n
JOIN pg_catalog.pg_class c ON c.relnamespace = n.oid
JOIN pg_catalog.pg_attribute att ON att.attrelid = c.oid
JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid
JOIN pg_catalog.pg_am a ON a.oid = c.relam
CROSS JOIN LATERAL (
SELECT ARRAY (SELECT opc.opcname
FROM unnest(i.indclass::oid[]) WITH ORDINALITY o(oid, ord)
JOIN pg_opclass opc ON opc.oid = o.oid
ORDER BY o.ord)
) opc(operator_classes)
WHERE n.nspname !~ '^pg_'
AND c.relkind = ANY (ARRAY['r', 't', 'i'])
group by
i.indrelid, c.relname, a.amname, opc.operator_classes, i.indisunique
ORDER BY 1, 2, 3, 4;
这非常有效,只是当通过转换另一列的数据创建索引时它会返回错误的列名称。例如,具有以下索引:
CREATE INDEX _ixtr_luxury_inventory_images ON public.luxury_inventory
USING gin (f_textarr2text(images) gin_trgm_ops)
返回:
table | luxury_inventory
index_name | _ixtr_luxury_inventory_images
index_type | gin
operator_classes | ['gin_trgm_ops']
is_unique | False
column_names | ['f_textarr2text']
我们得到f_textarr2text
作为列名称而不是images
。
我们怎样才能得到正确的column_names?如果这很重要的话,我正在使用 Postgres 16.2。
更新:这是建表SQL:
create table "luxury_inventory" (
id bigserial primary key,
images text[])
CREATE OR REPLACE FUNCTION "public".f_textarr2text(text[])
RETURNS text LANGUAGE sql IMMUTABLE AS $$SELECT array_to_string($1, ',')$$;