PostgreSQL Negative Integer Overflow

Question

I was doing some tests on Postgres using the tinyint extension when I came across something surprising regarding its range. On typing select -128::tinyint it gave me an ERROR: tinyint out of range message which was not what I was expecting at all.

Assuming negative numbers should be 1 greater (or is it less) than the positive maximum (127 for single byte integers) I thought it was a bug with the extension, however on trying this with non-extended numbers I found exactly the same thing was happening.

select -32768::smallint -> out of range

select -2147483648::integer -> out of range

select -9223372036854775808::bigint -> out of range

Referring to the numeric data type documentation (https://www.postgresql.org/docs/current/datatype-numeric.html) these numbers should all be possible - all negative numbers one less -32767, -2147483647, -9223372036854775807 work correctly so I am curious as to why this is happening, or does this even happen with other peoples copies.

I tried using both postgresql 10 and postgresql 11 on a ubuntu 16.x desktop.

Answer 1

I think this is because the cast operator :: has a higher precedence that the minus sign.

So -32768::smallint is executed as -1 * 32768::smallint which indeed is invalid.

Using parentheses fixes this: (-32768)::smallint or using the SQL standard cast() operator: cast(-32768 as smallint)