Why unsigned integer is not available in PostgreSQL?

Question

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.

Answer 1

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"