Why unsigned integer is not available in PostgreSQL?

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.

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?

Thanks.

Solution 1:

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

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.

Solution 2:

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

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.

DS1=# select (346346 :: uint2);

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

Solution 3:

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

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

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

Solution 4:

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.

When asked by someone about it, Tome Lane stated:

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.

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.

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.

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.

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.

Here is how I can implement a simple 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_in and uint8_out I must first define.

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 here and make it simple:

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.