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 )

expression::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

But:

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

All OK.

???

3 comments:

  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).

    ReplyDelete
    Replies
    1. Ah, found it: https://www.postgresql.org/docs/11/sql-syntax-lexical.html#SQL-PRECEDENCE

      Delete