为什么无符号整数在 PostgreSQL 中不可用？

Why unsigned integer is not available in PostgreSQL?

提问by Adrian Hoe

I came across this post (What is the difference between tinyint, smallint, mediumint, bigint and int in MySQL?) and realized that PostgreSQL does not support unsigned integer.

我看到这篇文章（MySQL 中的 tinyint、smallint、mediumint、bigint 和 int 有什么区别？）并意识到 PostgreSQL 不支持无符号整数。

Can anyone help to explain why is it so?

任何人都可以帮助解释为什么会这样吗？

Most of the time, I use unsigned integer as auto incremented primary key in MySQL. In such design, how can I overcome this when I port my database from MySQL to PostgreSQL?

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

Thanks.

谢谢。

采纳答案by Karl Tarbe

It is already answered why postgresql lacks unsigned types. However I would suggest to use domains for unsigned types.

已经回答了为什么 postgresql 缺少无符号类型。但是我建议将域用于无符号类型。

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

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) }

Domain is like a type but with an additional constraint.

域就像一个类型，但有一个额外的约束。

For an concrete example you could use

对于一个具体的例子，你可以使用

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

Here is what psql gives when I try to abuse the type.

这是当我尝试滥用类型时 psql 给出的内容。

DS1=# select (346346 :: uint2);

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

DS1=#选择（346346::uint2）；

错误：域 uint2 的值违反检查约束“uint2_check”

回答by Peter Eisentraut

It's not in the SQL standard, so the general urge to implement it is lower.

它不在 SQL 标准中，因此实现它的普遍冲动较低。

Having too many different integer types makes the type resolution system more fragile, so there is some resistance to adding more types into the mix.

拥有太多不同的整数类型会使类型解析系统更加脆弱，因此在混合中添加更多类型存在一些阻力。

That said, there is no reason why it couldn't be done. It's just a lot of work.

也就是说，没有理由不能做到。这只是很多工作。

回答by TriAnMan

You can use a CHECK constraint, e.g.:

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

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

Also, PostgreSQL has smallserial, serial, and bigserialtypes for auto-increment.

另外，PostgreSQL有smallserial，serial和bigserial类型自动递增。

回答by Gunther Schadow

The talk about DOMAINS is interesting but not relevant to the only possible origin of that question. The desire for unsigned ints is to double the range of ints with the same number of bits, it's an efficiency argument, not the desire to exclude negative numbers, everybody knows how to add a check constraint.

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

When asked by someone about it, Tome Lane stated:

当有人问起此事时，Tome Lane 说：

Basically, there is zero chance this will happen unless you can find a way of fitting them into the numeric promotion hierarchy that doesn't break a lot of existing applications. We have looked at this more than once, if memory serves, and failed to come up with a workable design that didn't seem to violate the POLA.

基本上，这种情况发生的可能性为零，除非您能找到一种方法将它们放入不会破坏许多现有应用程序的数字提升层次结构中。如果没记错的话，我们已经不止一次地研究过这个问题，但未能提出一个似乎没有违反 POLA 的可行设计。

What is the "POLA"? Google gave me 10 results that are meaningless. Not sure if it's politically incorrect thought and therefore censored. Why would this search term not yield any result? Whatever.

什么是“POLA”？Google 给了我 10 个毫无意义的结果。不确定这是否是上不正确的想法，因此被。为什么这个搜索词不会产生任何结果？任何。

You can implement unsigned ints as extension types without too much trouble. If you do it with C-functions, then there will be about no performance penalties at all. You won't need to extend the parser to deal with literals because PgSQL has such an easy way to interpret strings as literals, just write '4294966272'::uint4 as your literals. Casts shouldn't be a huge deal either. You don't even need to do range exceptions, you can just treat the semantics of '4294966273'::uint4::int as -1024. Or you can throw an error.

您可以将无符号整数实现为扩展类型而不会太麻烦。如果你用 C 函数来做，那么根本不会有性能损失。您不需要扩展解析器来处理文字，因为 PgSQL 有一种将字符串解释为文字的简单方法，只需将 '4294966272'::uint4 写为您的文字。演员也不应该是一个大问题。您甚至不需要进行范围异常，您只需将 '4294966273'::uint4::int 的语义视为 -1024。或者你可以抛出一个错误。

If I wanted this, I would have done it. But since I'm using Java on the other side of SQL, to me it is of little value since Java doesn't have those unsigned integers either. So I gain nothing. I'm already annoyed if I get a BigInteger from a bigint column, when it should fit into long.

如果我想要这个，我会做到的。但是因为我在 SQL 的另一边使用 Java，对我来说它没有什么价值，因为 Java 也没有那些无符号整数。所以我一无所获。如果我从 bigint 列中得到一个 BigInteger，而当它应该适合 long 时，我已经很生气了。

Another thing, if I did have the need to store 32 bit or 64 bit types, I can use PostgreSQL int4 or int8 respectively, just remembering that the natural order or arithmetic won't work reliably. But storing and retrieving is unaffected by that.

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

---

---

Here is how I can implement a simple unsigned int8:

下面是我如何实现一个简单的 unsigned int8：

First I will use

首先我会用

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
)

the minimal 2 functions uint8_inand uint8_outI must first define.

最小的 2 个函数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;

need to implement this in C uint8_funcs.c. So I go use the complex example from hereand make it simple:

需要在 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);
}

ah well, or you can just find it done already.

啊好吧，或者你可以发现它已经完成了。

回答by Kemin Zhou

According to the latest documentation, the singed integer is supported but no unsigned integer in the table. However, the serial type is kind of similar to unsigned except it starts from 1 not from zero. But the upper limit is the same as singed. So the system truly does not have unsigned support. As pointed out by Peter, the door is open to implement the unsigned version. The code may have to be updated a lot, just too much work from my experience working with C programming.

根据最新文档，支持有符号整数，但表中没有无符号整数。然而，串行类型有点类似于无符号，除了它从 1 开始而不是从 0 开始。但上限与烧焦相同。所以系统确实没有未签名的支持。正如彼得所指出的，实现未签名版本的大门是敞开的。代码可能需要更新很多，根据我在 C 编程方面的经验，工作量太大了。

https://www.postgresql.org/docs/10/datatype-numeric.html

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

回答by Bergi

Postgres does have an unsigned integer type that is unbeknownst to many: OID.

Postgres的确有一个无符号整数类型，它是不知情的许多：OID。

The oidtype is currently implemented as an unsigned four-byte integer. […]

The oidtype itself has few operations beyond comparison. It can be cast to integer, however, and then manipulated using the standard integer operators. (Beware of possible signed-versus-unsigned confusion if you do this.)

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

该oid类型本身有一些操作无法比拟的。但是，它可以转换为整数，然后使用标准整数运算符进行操作。（如果您这样做，请注意可能的有符号与无符号混淆。）

It is not a numeric typethough, and trying to do any arithmetic (or even bitwise operations) with it is going to fail. Also, it's just 4 bytes (INTEGER), there is no corresponding 8 byte (BIGINT) unsigned type.

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

So it's not really a good idea to use this yourself, and I agree with all the other answers that in a Postgresql database design you should always use an INTEGERor BIGINTcolumn for your serialprimary key - having it start in the negative (MINVALUE) or allowing it to wrap around (CYCLE) if you want to exhaust the full domain.

因此，自己使用它并不是一个好主意，我同意所有其他答案，即在 Postgresql 数据库设计中，您应该始终使用INTEGERorBIGINT列作为您的串行主键 - 让它以负数 ( MINVALUE)开头或允许它CYCLE如果您想用尽整个域，则环绕 ( )。

However, it is quite useful for input/output conversion, like your migration from another DBMS. Inserting the value 2147483648into an integer column will lead to an "ERROR: integer out of range", while using the expression 2147483648::OIDworks just fine.
Similarly, when selecting an integer column as text with mycolumn::TEXT, you will get negative values at some point, but with mycolumn::OID::TEXTyou will always get a natural number.

但是，它对于输入/输出转换非常有用，例如从另一个 DBMS 迁移。将值2147483648插入整数列将导致“错误：整数超出范围”，而使用该表达式2147483648::OID工作正常。
Similarly, when selecting an integer column as text with mycolumn::TEXT, you will get negative values at some point, but with mycolumn::OID::TEXTyou will always get a natural number.

See an example at dbfiddle.uk.

请参阅dbfiddle.uk 上的示例。