Thursday, May 2, 2019

Not all CASTs are created equal?

Can somebody explain this?

[Solved: See the comments section]

PostgreSQL 11.2.

The documentation says:

"A type cast specifies a conversion from one data type to another. PostgreSQL accepts two equivalent syntaxes for type casts:

CAST ( expression AS type )


The CAST syntax conforms to SQL; the syntax with :: is historical PostgreSQL usage."

But when I test the lower limits of PostgreSQL's integer types, strange things happen.

select cast(-9223372036854775808 as bigint);
select cast(-2147483648 as integer);
select cast(-32768 as smallint);

All OK.

select -9223372036854775808::bigint;
select -2147483648::integer;
select -32768::smallint;

All fail with SQL Error [22003]: ERROR: out of range


select -9223372036854775807::bigint;
select -2147483647::integer;
select -32767::smallint;

All OK.



  1. Consider that :: has a very high operator precedence, much higher than that of the unary - operator.

    cast(-32768 as smallint) is equivalent to (-32768)::smallint, and NOT to -32768::smallint which parses as -(32768::smallint).

    1. Ah, found it: