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.

user330315 · Accepted Answer · 2018-11-28 13:42:34Z

6

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)

answered Nov 28, 2018 at 13:42

user330315

Sign up to request clarification or add additional context in comments.

1 Comment

Lucas Over a year ago

That makes a lot of sense, inputting the number as a string e.g. select '-32768'::smallint will pass correctly also so I can only assume it would be the function ordering as you mentioned.

Collectives™ on Stack Overflow

PostgreSQL Negative Integer Overflow

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related