Negative numbers to DOMAIN casting
Hi,
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);
?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
If I put number inside brackets I get check error:
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"
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"
I tested this on PG 9.4 and 9.5.
Is this expected behavior?
Regards,
Matija Lesar
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Hi Tom,
thank you for the explanation.
Regards,
Matija Lesar
On 14 December 2016 at 15:53, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Show quoted text
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. Seehttps://www.postgresql.org/docs/9.5/static/sql-syntax-
lexical.html#SQL-PRECEDENCEregards, tom lane