在 PostgreSQL 中，可以使用系統表來取得參與索引的欄位。請依照下列步驟操作：
SELECT
t.relname AS table_name, -- Name of the indexed table
i.relname AS index_name, -- Name of the index
a.attname AS column_name -- Indexed column name
FROM
pg_class t -- Catalog table for tables
JOIN
pg_class i ON t.oid = i.indrelid -- Catalog table for indexes
JOIN
pg_index ix ON i.oid = ix.indexrelid -- Index information
JOIN
pg_attribute a ON t.oid = a.attrelid AND a.attnum = ANY(ix.indkey) -- Join with table attributes
WHERE
t.relkind = 'r' -- Restrict to regular tables
AND t.relname LIKE 'test%'; -- Filter based on pattern (optional)
ORDER BY
t.relname, -- Sort by table name
i.relname; -- Then sort by index name範例輸出：
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, -- Name of the indexed table
i.relname AS index_name, -- Name of the index
array_to_string(array_agg(a.attname), ', ') AS column_names -- Concatenate column names
FROM
pg_class t -- Catalog table for tables
JOIN
pg_class i ON t.oid = i.indrelid -- Catalog table for indexes
JOIN
pg_index ix ON i.oid = ix.indexrelid -- Index information
JOIN
pg_attribute a ON t.oid = a.attrelid AND a.attnum = ANY(ix.indkey) -- Join with table attributes
WHERE
t.relkind = 'r' -- Restrict to regular tables
AND t.relname LIKE 'test%'; -- Filter based on pattern (optional)
GROUP BY
t.relname, -- Group by table name
i.relname -- Then group by index name
ORDER BY
t.relname, -- Sort by table name
i.relname; -- Then sort by index name範例輸出：
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
以上是如何識別 PostgreSQL 索引中包含的欄位？的詳細內容。更多資訊請關注PHP中文網其他相關文章！
