[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:
But:
select -9223372036854775807::bigint;
select -2147483647::integer;
select -32767::smallint;
All OK.
???
Consider that :: has a very high operator precedence, much higher than that of the unary - operator.
ReplyDeletecast(-32768 as smallint) is equivalent to (-32768)::smallint, and NOT to -32768::smallint which parses as -(32768::smallint).
Ah, found it: https://www.postgresql.org/docs/11/sql-syntax-lexical.html#SQL-PRECEDENCE
DeleteDead-on!
Delete