Per Postgres documentation, an integer type is defined between -2147483648 and +2147483647.
I thought that these boundaries were inclusive, but if I try:
select -2147483648 = -2147483648::int4
提高Integer out of range error.
上限似乎被正确设定:
# select 2147483647 = 2147483647::int4;
?column?
----------
t
(1 row)
如果我将下限增加一，它也可以正常工作:
# select -2147483647 = -2147483647::int4;
?column?
----------
t
(1 row)
同样的结果适用于smallint.
TLDR: operator precedence.
乍一看，这很棘手.对于smallint和bigint，同样的下限投射似乎也失败了:
SELECT -32768::int2; -- fails
SELECT -2147483648::int4; -- fails
SELECT -9223372036854775808::int8; -- fails
但是外表是骗人的.这就是真正发生的事情:
SELECT - (2147483648::int4);
-被认为是"unary minus"运算符，它只在105 ::("PostgreSQL-style typecast")中起作用.由于integer(int4)的范围是
SELECT 2147483648::int4;
db<>fiddle 100
请使用以下选项之一:
SELECT '-2147483648'::int4;
SELECT int '-2147483648';
也就是说，再多一点efficient，因为这只是一个强制转换，而不是强制转换+否定操作.
你甚至可以:
SELECT (-2147483648)::int4;
只是为了推翻运算符优先级.但最后一个看起来很尴尬.而且效率略低.:)
相关: