postgresql - 从 PostgreSQL 中 uuid 的大端创建大整数

Question

我有一个第三方应用程序连接到我的 PostgreSQL 数据库中的一个 View 。它要求 View 具有主键但不能处理 UUID 类型(这是 View 的主键)。如果 UUID 作为 View 中的文本提供，它也无法将 UUID 作为主键处理。

我想做的是将 UUID 转换为数字并将其用作主键。然而，

SELECT x'14607158d3b14ac0b0d82a9a5a9e8f6e'::bigint

失败，因为数字超出范围。

因此，我想使用 SQL 获取 UUID 的大端并创建一个 int8/bigint。我应该澄清，维持秩序是“可取的”，但我知道这样做会改变一些秩序。

我试过:

SELECT x(substring(UUID::text from 1 for 16))::bigint

但是用于转换十六进制的 x 运算符似乎不喜欢括号。我把它抽象成一个函数但是

SELECT hex_to_int(substring(UUID::text from 1 for 16))::bigint

仍然失败。

如何从 UUID 的“大端”部分获取 bigint？

Answer 1

快速且无需动态 SQL

将文本表示中 UUID 的前 16 位十六进制数字转换为 bitstring bit(64)并将其转换为 bigint。见:

• Convert hex in text representation to decimal number

方便的是，右侧多余的十六进制数字会在转换为 bit(64) 时被自动截断 - 这正是我们所需要的。

Postgres 接受各种格式的输入。您给定的字符串文字就是其中之一:

14607158d3b14ac0b0d82a9a5a9e8f6e

UUID 的默认文本表示(以及 Postgres 中数据类型 uuid 的 text 输出)在预定义的位置添加连字符:

14607158-d3b1-4ac0-b0d8-2a9a5a9e8f6e

The manual:

A UUID is written as a sequence of lower-case hexadecimal digits, inseveral groups separated by hyphens, specifically a group of 8 digitsfollowed by three groups of 4 digits followed by a group of 12 digits,for a total of 32 digits representing the 128 bits.

如果输入格式可能不同，请先去除连字符以确保:

SELECT ('x' || translate(uuid_as_string, '-', ''))::bit(64)::bigint;

Actor 实际uuid输入 uuid::text。

db<> fiddle here

请注意，Postgres 使用有符号整数，因此 bigint 溢出为上半部分的负数 - 这与此目的无关。

数据库设计

如果可能的话，添加一个 bigserial列到基础表并改用它。