PostgreSQL 9.0和9.1之间的枚举类型文字的顺序差异 [英] The difference in ordering of enum type literals between PostgreSQL 9.0 and 9.1

问题描述

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

There has been some curious update in the way enum types work between PostgreSQL 9.0 and 9.1. The pg_catalog.pg_enum table has a new column enumsortorder in PostgreSQL 9.1. This order seems to override the previous enum ordering based on OIDs.

特定枚举类型的OID为保证按类型应该排序的方式进行排序，但是不能保证不相关的枚举类型的OID的排序。

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文档

PostgreSQL 9.1 Documentation

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

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.

创建枚举类型时，将为其成员分配排序位置1 ..n。但是，以后添加的成员可能会得到枚举顺序的负值或分数值。这些值的唯一要求是它们在每种枚举类型中都应正确排序并唯一。

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代码生成器，我正在阅读 pg_catalog.pg_enum 表，按OID排序枚举文字，在PostgreSQL 9.0中指定的方式。使用udpated规范，似乎我应该通过 enumsortorder 来对文字进行排序，因为它尊重

My question

For the jOOQ code generator, I'm reading the pg_catalog.pg_enum table, ordering enum literals by OID, the way it was specified in PostgreSQL 9.0. With the udpated specification, it seems that I should order the literals by enumsortorder, which seems to behave differently as it respects enum literal insertions "in the middle".

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

What's the most reliable, cross-version compatible way to read these enum literals from the pg_catalog?

推荐答案

我认为您需要检查PostgreSQL版本并适当更改行为，或使用不会触及目录，以确定顺序。

I think you'll need to check the PostgreSQL version and change behaviour appropriately, or use SQL that doesn't touch the catalog to determine the ordering.

给定虚拟枚举，给后者一个想法：

An idea for the latter, given dummy enum:

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

是 ORDER BY 的演员表使用8.4及更高版本中提供的 row_number 窗口函数将枚举标签转换为枚举类型的值：

is to ORDER BY the cast of the enum label to values of the enum type using the row_number window function available in 8.4 and newer:

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 进行排序，在较新的版本中，它将使用枚举顺序，但是您不必在意这两种方式，您只是告诉PostgreSQL请按正确的顺序将它们分类。

This gets you the labels ordered by a sort key. In older Pg versions Pg will just sort by the oid of the enum values, in newer versions it'll use the enumsortorder, but you don't have to care either way, you've just told PostgreSQL "sort these into the correct order please".

或者如果您只需要服务器期望的顺序，请输入：

Or if you just need them in the order the server expects, write:

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

这篇关于PostgreSQL 9.0和9.1之间的枚举类型文字的顺序差异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！