Обсуждение: [GENERAL] Negative numbers to DOMAIN casting
Hi,
I have uint4 domain created like this:
CREATE DOMAIN uint4 AS int8
CHECK(VALUE BETWEEN 0 AND 4294967295);
CREATE DOMAIN uint4 AS int8
CHECK(VALUE BETWEEN 0 AND 4294967295);
If I try to cast negative number to this domain check constraint is not validated:
SELECT -1::uint4, pg_typeof(-1::uint4), 1::uint4, pg_typeof(1::uint4);
?column? | pg_typeof | uint4 | pg_typeof
----------+-----------+-------+-----------
-1 | bigint | 1 | uint4
SELECT -1::uint4, pg_typeof(-1::uint4), 1::uint4, pg_typeof(1::uint4);
?column? | pg_typeof | uint4 | pg_typeof
----------+-----------+-------+-----------
-1 | bigint | 1 | uint4
Also the pg_typeof returns bigint, but if i do int8 to int2 conversion pg_typeof returns right type:
SELECT pg_typeof(-1::int4::int2);
pg_typeof
-----------
smallint
SELECT pg_typeof(-1::int4::int2);
pg_typeof
-----------
smallint
If I put number inside brackets I get check error:
select (-1)::uint4;
ERROR: value for domain uint4 violates check constraint "uint4_check"
select (-1)::uint4;
ERROR: value for domain uint4 violates check constraint "uint4_check"
The same error is thrown if I use CAST:
SELECT CAST(-1 AS uint4);
ERROR: value for domain uint4 violates check constraint "uint4_check"
SELECT CAST(-1 AS uint4);
ERROR: value for domain uint4 violates check constraint "uint4_check"
And also if domain is used in table then check is also working as expected:
CREATE TABLE test(i uint4);
INSERT INTO test VALUES(-1);
ERROR: value for domain uint4 violates check constraint "uint4_check"
CREATE TABLE test(i uint4);
INSERT INTO test VALUES(-1);
ERROR: value for domain uint4 violates check constraint "uint4_check"
I tested this on PG 9.4 and 9.5.
Is this expected behavior?
Matija Lesar <matija.lesar@gmail.com> writes: > I have uint4 domain created like this: > CREATE DOMAIN uint4 AS int8 > CHECK(VALUE BETWEEN 0 AND 4294967295); > If I try to cast negative number to this domain check constraint is not > validated: > SELECT -1::uint4, pg_typeof(-1::uint4), 1::uint4, pg_typeof(1::uint4); :: binds tighter than minus, so you would need to write these like "(-1)::uint4" to get the behavior you're expecting. See https://www.postgresql.org/docs/9.5/static/sql-syntax-lexical.html#SQL-PRECEDENCE regards, tom lane
Hi Tom,
thank you for the explanation.On 14 December 2016 at 15:53, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Matija Lesar <matija.lesar@gmail.com> writes:
> I have uint4 domain created like this:
> CREATE DOMAIN uint4 AS int8
> CHECK(VALUE BETWEEN 0 AND 4294967295);
> If I try to cast negative number to this domain check constraint is not
> validated:
> SELECT -1::uint4, pg_typeof(-1::uint4), 1::uint4, pg_typeof(1::uint4);
:: binds tighter than minus, so you would need to write these like
"(-1)::uint4" to get the behavior you're expecting. See
https://www.postgresql.org/docs/9.5/static/sql-syntax- lexical.html#SQL-PRECEDENCE
regards, tom lane