为什么PostgreSQL没有无符号整数？

Question

我看到了这篇帖子（MySQL中tinyint、smallint、mediumint、bigint和int的区别是什么？），意识到PostgreSQL不支持无符号整数。

有人能解释一下为什么吗？

大多数时候，我在MySQL中将无符号整数用作自增的主键。在这样的设计中，当我将数据库从MySQL迁移到PostgreSQL时，该如何克服这个问题？

谢谢。

- Adrian Hoe

---

还没有，但很快我们会考虑转移到PostgreSQL。 - Adrian Hoe

我认为这里不是询问为什么做出某些决定的最佳场所，可以考虑在PostgreSQL邮件列表中提问。如果你想要自动递增的值，请使用serial(1到2147483647)或者bigserial(1到9223372036854775807)。一个带符号的64位整数可能提供了足够的空间。 - mu is too short

谢谢@muistooshort。这回答了主键问题。但是对于既不是自动增量又不是主键的无符号整数类型怎么办？我有一些列存储无符号整数，其范围从0到2^32。 - Adrian Hoe

如果您已经对MySQL有很大的投资，那么使用它是唯一的原因。否则，建议您快速浏览PostgreSQL文档以更好地了解其功能。PostgreSQL速度快，拥有许多有用的功能，并由对数据非常谨慎的人构建。当然，这只是我的个人意见 :)。 - mu is too short

再次感谢@muistooshort提供的指导。 - Adrian Hoe

Answer 1

这并不在SQL标准中，因此实现它的一般愿望较低。

拥有过多不同的整数类型会使类型解析系统变得更加脆弱，因此添加更多类型会受到一定的抵制。

尽管如此，没有理由不能这样做。只是需要大量的工作。

- Peter Eisentraut

---

这个问题非常受欢迎，我已经着手解决它：https://github.com/petere/pguint - Peter Eisentraut

拥有无符号整数字面量的输入/输出转换将非常有用。甚至只需要一个to_char模式。 - Bergi

这也解释了为什么Postgres中没有tinyint吗？（假设我们知道值在范围内，有时可能更有效） - Saleh

Answer 2

已经回答了为什么PostgreSQL缺少无符号类型的问题。但是我建议使用域来表示无符号类型。

http://www.postgresql.org/docs/9.4/static/sql-createdomain.html

 CREATE DOMAIN name [ AS ] data_type
    [ COLLATE collation ]
    [ DEFAULT expression ]
    [ constraint [ ... ] ]
 where constraint is:
 [ CONSTRAINT constraint_name ]
 { NOT NULL | NULL | CHECK (expression) }

域与类型类似，但具有附加约束条件。

以具体示例为例，您可以使用

CREATE DOMAIN uint2 AS int4
   CHECK(VALUE >= 0 AND VALUE < 65536);

当我尝试滥用该类型时，以下是psql提供的结果。

DS1=# select (346346 :: uint2);

ERROR: value for domain uint2 violates check constraint "uint2_check"

- Karl Tarbe

---

但我猜每次我们想要一个无符号列时使用这个域会在插入/更新时增加开销。最好只在真正需要的情况下使用它（这种情况很少），并且习惯于数据类型不设置我们所需的下限这一思想。毕竟，它还设置了通常从逻辑上讲无意义的上限。数字类型并不是为了强制执行我们应用程序的约束而设计的。 - Federico Razzoli

这种方法唯一的问题是你浪费了15位未使用的数据存储空间。更不用说这个检查还会耗费一定的效率。更好的解决方案应该是Postgres添加无符号整数类型作为一流类型。在一个有2000万条记录的表中，如果像这样创建一个索引字段，你会浪费40MB的未使用位空间。如果你在另外20张表中也这么做，那么你现在就浪费了800MB的空间。 - tpartee

Answer 3

你可以使用CHECK约束，例如：

CREATE TABLE products (
    id integer,
    name text,
    price numeric CHECK (price > 0)
);

此外，PostgreSQL 还有 serial、smallserial 和 bigserial 类型来实现自增。

- TriAnMan

---

有一件事需要提醒，使用CHECK的列中不能有任何NULL值。 - Minutis

@Minutis 你确定不能有 x IS NULL OR x BETWEEN 4 AND 40 吗？ - jgmjgm

这意味着它不会给您与无符号整数相同的分辨率。也就是说，无符号整数可以达到2^32-1，而有符号整数只能达到2^31-1。 - JukesOnYou

NULL和CHECK是完全正交的。您可以有带有或不带有CHECK的NULL/NOT NULL列。只需注意，根据https://www.postgresql.org/docs/9.4/ddl-constraints.html中的文档，返回`NULL`的`CHECK`将计算为TRUE，因此如果您真的想要防止NULL，则应改用`NOT NULL（或除CHECK`之外） 。 - flaviovs

使用 CHECK 无法让我将 IPv4 地址存储在 integer 中（至少不能让它们随机变为正数或负数..）。 - hanshenrik

Answer 4

关于“域”的讨论很有趣，但与该问题唯一可能的来源无关。对无符号整数的渴望是为了增加具有相同位数的整数范围，这是一个效率论据，而不是排除负数的愿望，每个人都知道如何添加检查约束。

当某人询问时，Tome Lane说：

基本上，除非你能找到一种将它们放入数字提升层次结构中且不会破坏许多现有应用程序的方法，否则不可能发生这种情况。如果我没记错的话，我们已经多次考虑过这个问题，并未能想出一个可行的设计，似乎违反了POLA。

什么是“POLA”？Google给我10个毫无意义的结果。不确定是否涉及政治不正确的想法，因此被审查了。为什么这个搜索词没有产生任何结果？无论怎样。

你可以很容易地将无符号整数实现为扩展类型。如果使用C函数实现，则几乎不会有性能损失。你不需要扩展解析器来处理字面量，因为PgSQL有一种简单的方法将字符串解释为字面量，只需编写'4294966272' :: uint4作为您的字面量即可。转换也不应该是一个大问题。您甚至不需要进行范围异常处理，只需将'4294966273' :: uint4 :: int的语义视为-1024即可。或者你可以抛出错误。

如果我想要这个，我早就这么做了。但由于我在SQL的另一侧使用Java，对我来说这没有什么价值，因为Java也没有这些无符号整数。所以我得不到任何好处。当bigint列应该适合long时，如果我从bigint列中获取BigInteger，我已经感到非常烦恼。

另外，如果我确实需要存储32位或64位类型，我可以分别使用PostgreSQL int4或int8，只需记住自然顺序或算术运算不可靠即可。但是存储和检索不受此影响。

---

这里是如何实现一个简单的无符号int8：

首先我将使用

CREATE TYPE name (
    INPUT = uint8_in,
    OUTPUT = uint8_out
    [, RECEIVE = uint8_receive ]
    [, SEND = uint8_send ]
    [, ANALYZE = uint8_analyze ]
    , INTERNALLENGTH = 8
    , PASSEDBYVALUE ]
    , ALIGNMENT = 8
    , STORAGE = plain
    , CATEGORY = N
    , PREFERRED = false
    , DEFAULT = null
)

我必须首先定义的是最小的两个函数uint8_in和uint8_out。

CREATE FUNCTION uint8_in(cstring)
    RETURNS uint8
    AS 'uint8_funcs'
    LANGUAGE C IMMUTABLE STRICT;

CREATE FUNCTION uint64_out(complex)
    RETURNS cstring
    AS 'uint8_funcs'
    LANGUAGE C IMMUTABLE STRICT;

需要在C语言的uint8_funcs.c文件中实现此功能。因此，我使用这里的复杂示例并将其简化：

PG_FUNCTION_INFO_V1(complex_in);

Datum complex_in(PG_FUNCTION_ARGS) {
    char       *str = PG_GETARG_CSTRING(0);
    uint64_t   result;

    if(sscanf(str, "%llx" , &result) != 1)
        ereport(ERROR,
                (errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
                 errmsg("invalid input syntax for uint8: \"%s\"", str)));

    return (Datum)SET_8_BYTES(result);
}

嗯，或者你可以直接找已经完成的。

- Gunther Schadow

---

我猜 POLA 是“最少惊讶原则”。它意味着改变有可能以意料之外的方式改变现有的行为。 - Doctor Eval

Answer 5

根据最新文件，该表支持有符号整数但不支持无符号整数。但是，串行类型与无符号类型有点相似，只是从1而不是从零开始。但上限与有符号相同。因此系统确实不支持无符号。正如Peter指出的那样，可以实现无符号版本。从我的C编程经验来看，代码可能需要进行大量更新，这太麻烦了。

参考链接：https://www.postgresql.org/docs/10/datatype-numeric.html

integer     4 bytes     typical choice for integer  -2147483648 to [phone]
serial      4 bytes     autoincrementing integer    1 to 2147483647

- Kemin Zhou

Answer 6

Postgres确实有一个无符号整数类型，但很多人并不知道：OID。

oid类型当前实现为无符号四字节整数。[...]

oid类型本身除了比较操作外几乎没有其他操作。但是，它可以转换为整数，然后使用标准整数运算符进行操作（如果这样做，请注意可能存在的有符号与无符号混淆）。

虽然它不是数字类型，但尝试对其进行任何算术运算（甚至是按位运算）都会失败。此外，它只有4个字节（INTEGER），没有相应的8个字节（BIGINT）无符号类型。

因此，自己使用它并不是一个好主意，我同意所有其他答案，在Postgresql数据库设计中，您应该始终为您的序列主键使用INTEGER或BIGINT列——如果您想要耗尽完整域，则使其从负数（MINVALUE）开始或允许其环绕（CYCLE）。

但是，它非常适用于输入/输出转换，比如从另一个DBMS迁移。将值2147483648插入整数列将导致"ERROR: integer out of range"，而使用表达式2147483648::OID则完全可以。
类似地，当将整数列选择为文本时，您将在某个时候得到负值，但是使用mycolumn::OID::TEXT，您将始终获得自然数。

请参见dbfiddle.uk上的示例。

- Bergi

---

如果您不需要操作，那么使用OID的唯一价值就是可以使您的排序顺序正常工作。如果这正是您所需要的，那很好。但很快就会有人想要一个uint8，然后他们也会迷失方向。底线是，要存储32位或64位的值，您只需分别使用int4和int8即可，只需要小心操作即可。但编写扩展程序很容易。 - Gunther Schadow