sql - PostgreSQL 9.0 和 9.1 枚举类型字面量排序的区别

Question

enum 类型在 PostgreSQL 9.0 和 9.1 之间的工作方式有一些奇怪的更新。 pg_catalog.pg_enum 表在 PostgreSQL 9.1 中有一个新列 enumsortorder。此顺序似乎覆盖了之前基于 OID 的枚举顺序。

PostgreSQL 9.0 Documentation

The OIDs for a particular enum type are guaranteed to be ordered in the way the type should sort, but there is no guarantee about the ordering of OIDs of unrelated enum types.

PostgreSQL 9.1 Documentation

The OIDs for pg_enum rows follow a special rule: even-numbered OIDs are guaranteed to be ordered in the same way as the sort ordering of their enum type. That is, if two even OIDs belong to the same enum type, the smaller OID must have the smaller enumsortorder value. Odd-numbered OID values need bear no relationship to the sort order. This rule allows the enum comparison routines to avoid catalog lookups in many common cases. The routines that create and alter enum types attempt to assign even OIDs to enum values whenever possible.

When an enum type is created, its members are assigned sort-order positions 1..n. But members added later might be given negative or fractional values of enumsortorder. The only requirement on these values is that they be correctly ordered and unique within each enum type.

我的问题

对于jOOQ code generator ，我正在读取 pg_catalog.pg_enum 表，按 OID 对枚举文字进行排序，这是在 PostgreSQL 9.0 中指定的方式。使用更新的规范，我似乎应该按 enumsortorder 对文字进行排序，这似乎表现不同，因为它尊重“中间”的枚举文字插入。

从 pg_catalog 中读取这些枚举文字的最可靠、跨版本兼容的方法是什么？

Answer 1

我认为您需要检查 PostgreSQL 版本并适本地更改行为，或者使用不触及目录的 SQL 来确定顺序。

后者的想法，给定虚拟枚举:

CREATE TYPE test_enum AS ENUM ('z','x','y');
ALTER TYPE test_enum ADD VALUE 'a' BEFORE 'x';

是 ORDER BY 使用 8.4 和更新版本中可用的 row_number 窗口函数将枚举标签转换为枚举类型的值:

SELECT enumlabel, row_number() OVER (ORDER BY enumlabel::test_enum) AS sort_key
FROM pg_catalog.pg_enum
WHERE enumtypid = 'test_enum'::regtype;

这将为您提供按排序键排序的标签。在较旧的 Pg 版本中，Pg 将仅按枚举值的 oid 排序，在较新的版本中，它将使用 enumsortorder，但您不必关心任何一种方式，您只是告诉 PostgreSQL “请将这些按正确的顺序排序”。

或者如果您只需要按照服务器期望的顺序使用它们，请写:

SELECT enumlabel
FROM pg_catalog.pg_enum
WHERE enumtypid = 'test_enum'::regtype
ORDER BY enumlabel::test_enum