Negative numbers to DOMAIN casting

Started by Matija Lesarover 9 years ago3 messagesgeneral
Jump to latest
#1Matija Lesar
matija.lesar@gmail.com

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

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Matija Lesar (#1)
Re: Negative numbers to DOMAIN casting

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

#3Matija Lesar
matija.lesar@gmail.com
In reply to: Tom Lane (#2)
Re: Negative numbers to DOMAIN casting

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

https://www.postgresql.org/docs/9.5/static/sql-syntax-
lexical.html#SQL-PRECEDENCE

regards, tom lane