Re: BUG #14986: -2147483648 is minimum value of integer but -2147483648::integer fails (out of range).

On Wed, Dec 20, 2017 at 1:20 PM, PG Bug reporting form <
noreply(at)postgresql(dot)org> wrote:

> The following bug has been logged on the website:
>
> Bug reference: 14986
> Logged by: Indrek Loolaid
> Email address: binoternary(at)gmail(dot)com
> PostgreSQL version: 10.1
> Operating system: Ubuntu 16.04.3 LTS
> Description:
>
> Documentation in
> https://www.postgresql.org/docs/current/static/datatype-numeric.html
> states
> that the range for integer type is -2147483648 to +2147483647.
>
> However casting the minimum value literal with :: syntax to integer fails
> (ERROR: integer out of range).
>
> postgres=# select version();
> version
>
> ------------------------------------------------------------
> ----------------------------------------------------
> PostgreSQL 10.1 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu
> 5.4.0-6ubuntu1~16.04.4) 5.4.0 20160609, 64-bit
> (1 row)
>
>
> postgres=# select -2147483648::integer;
> ERROR: integer out of range
>
> postgres=# select cast(-2147483648 as integer);
> int4
> -------------
> -2147483648
> (1 row)
>
> postgres=# select (select -2147483648)::integer;
> ?column?
> -------------
> -2147483648
> (1 row)
>
>
> The expected outome is that the first query returns the same result as the
> other two.
> Bigint and smallint types have the same issue.
>

In the first query, you are casting 2147483648 to integer, and then
applying the minus. So it overflows the positive integer. You need ()
around it:

postgres=# select (-2147483648)::integer;
int4
-------------
-2147483648
(1 row)

--
Magnus Hagander
Me: https://www.hagander.net/ <http://www.hagander.net/>
Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/>