Lower bound for Postgres integer type out of range?

Question

Per Postgres documentation, an integer type is defined between -2147483648 and +2147483647.
I thought that these boundaries were inclusive, but if I try:

select -2147483648 = -2147483648::int4

an Integer out of range error is raised.

The upper bound seems to be casted properly:

# select 2147483647 = 2147483647::int4;
 ?column? 
----------
 t
(1 row)

And if I increase the lower bound by one it works fine as well:

# select -2147483647 = -2147483647::int4;
 ?column? 
----------
 t
(1 row)

The same result is applied to smallint. Is there something obvious that I'm missing here, or are lower bounds excluded for Postgres numeric types?

Answer 1

TLDR: operator precedence.

This is tricky at first sight. The same cast of the lower bound seemingly fails for smallint and bigint, too:

SELECT -32768::int2; -- fails
SELECT -2147483648::int4; -- fails
SELECT -9223372036854775808::int8; -- fails

But looks are deceiving. This is what really happens:

SELECT - (2147483648::int4);

- is taken to be "unary minus" operator, which only kicks in after :: (the "PostgreSQL-style typecast"). And since the range of integer (int4) is -2147483648 to +2147483647 as you quoted accurately, the expression fails at:

SELECT 2147483648::int4;

db<>fiddle here

Use one of these instead:

SELECT '-2147483648'::int4;
SELECT int '-2147483648';

Also ever so slightly more efficient, since that's just a cast, not a cast + negation operation.

You could even:

SELECT (-2147483648)::int4;

Just to overrule operator precedence. But the last one looks awkward. And it's slightly less efficient. :)

Related:

• Postgres data type cast