PostgreSQL 9.0和9.1中枚举类型字面量排序的差异

Question

PostgreSQL 9.0和9.1中枚举类型字面量排序的差异

sqlpostgresqlpostgresql-9.1jooqpostgresql-9.0

5

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

PostgreSQL 9.0文档

对于特定的枚举类型，OID保证按照类型应该进行排序，但是与不相关的枚举类型的OID的排序没有保证。

PostgreSQL 9.1文档

pg_enum行的OID遵循一个特殊规则：偶数OID保证按其枚举类型的排序顺序进行排序。也就是说，如果两个偶数OID属于同一枚举类型，则较小的OID必须具有较小的enumsortorder值。奇数OID值与排序顺序无关。这个规则允许枚举比较例程在许多常见情况下避免目录查找。创建和更改枚举类型的例程尝试在可能的情况下将偶数OID分配给枚举值。

创建枚举类型时，其成员被分配排序位置1..n。但稍后添加的成员可能会被赋予负或分数值的enumsortorder。对这些值的唯一要求是它们在每个枚举类型内正确排序并且唯一。

我的问题

对于jOOQ代码生成器，我正在读取pg_catalog.pg_enum表，按OID排序枚举文字，这是在PostgreSQL 9.0中指定的方式。根据更新的规范，似乎我应该按enumsortorder排序文字，因为它似乎会以不同的方式尊重“中间”插入的枚举文字。

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

- Lukas Eder

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Craig Ringer · Accepted Answer

我认为您需要检查PostgreSQL的版本并相应地更改行为，或使用不涉及目录的SQL来确定排序。

对于后者的建议，可以使用虚拟枚举:

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

将枚举标签的类型转换为枚举类型的值，使用8.4及更高版本中可用的row_number窗口函数进行ORDER BY：

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