3

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.

Lucas
  • 600
  • 3
  • 9

1 Answers1

5

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)

  • 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. – Lucas Nov 28 '18 at 14:09