postgresql - 如何从 Postgresql 获取索引列顺序(ASC，DESC，NULLS FIRST ....)？

Question

我必须检索索引中涉及的列的顺序。使用函数 pg_get_indexdef() 我可以获得索引的定义，如下所示，

"CREATE INDEX test ON ravi1.table_with_index USING btree ("Column1" DESC, "Column3" DESC, "Column4") WITH (fillfactor=60)"

这里的定义是说 Column1 和 Column3 是降序的，Column4 是升序的。

有了字符串中的这些数据，我必须进行解析以获得列排序顺序。

是否有任何替代方法，以便我能够获取值，即列顺序。

现在我正在使用以下查询获取与各个索引关联的列

SELECT ARRAY(SELECT pg_get_indexdef(idx.indexrelid, k + 1, true) FROM
generate_subscripts(idx.indkey, 1) as k ORDER BY k ) as index_members,
idx.indexprs IS NOT NULL as indexprs
FROM pg_index as idx
JOIN pg_class as i ON i.oid = idx.indexrelid
JOIN pg_namespace as ns ON ns.oid = i.relnamespace
JOIN pg_class as t ON t.oid = idx.indrelid
where ns.nspname = 'schema' and t.relname ='table' and i.relname ='index'

在同一个查询中，是否也需要注意列顺序？

这对解决问题有很大帮助，否则我必须编写一些解析器来从 pg_get_indexdef() 函数中获取值。

谢谢，

拉维

Answer 1

JDBC 驱动程序使用更简单的查询，它会返回列是定义为 ASC 还是 DESC

以下或多或少是驱动程序源代码的逐字副本。我删除了一些仅 JDBC 的列以使其更“通用”。

SELECT ct.relname AS TABLE_NAME, 
       i.indisunique, 
       ci.relname AS INDEX_NAME, 
       (i.keys).n AS ORDINAL_POSITION, 
       pg_catalog.pg_get_indexdef(ci.oid, (i.keys).n, false) AS COLUMN_NAME, 
       CASE am.amcanorder 
         WHEN true THEN CASE i.indoption[(i.keys).n - 1] & 1 
           WHEN 1 THEN 'DESC' 
           ELSE 'ASC' 
         END 
         ELSE NULL 
       END AS ASC_OR_DESC,
      pg_catalog.pg_get_expr(i.indpred, i.indrelid) AS FILTER_CONDITION 
FROM pg_catalog.pg_class ct 
  JOIN pg_catalog.pg_namespace n ON (ct.relnamespace = n.oid) 
  JOIN (SELECT i.indexrelid, i.indrelid, i.indoption, 
          i.indisunique, i.indisclustered, i.indpred, 
          i.indexprs, 
          information_schema._pg_expandarray(i.indkey) AS keys 
        FROM pg_catalog.pg_index i) i 
    ON (ct.oid = i.indrelid) 
  JOIN pg_catalog.pg_class ci ON (ci.oid = i.indexrelid) 
  JOIN pg_catalog.pg_am am ON (ci.relam = am.oid) 
WHERE n.nspname = 'some_schema'
AND ct.relname = 'some_table'

过时的警告:从 PostgreSQL 9.6 开始，pg_am 上的列不再可用。