Negative numbers to DOMAIN casting

From: Matija Lesar <matija(dot)lesar(at)gmail(dot)com>
To: PostgreSQL General <pgsql-general(at)postgresql(dot)org>
Subject: Negative numbers to DOMAIN casting
Date: 2016-12-14 10:54:11
Message-ID: CAPx3hmM3_RjqrEA3yLSXTytN+U9ThnPEhjF9rQAxh=sxi1mNyA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Mikhail 2016-12-14 11:35:24 Re: pg_upgrade 9.0 to 9.6
Previous Message Thomas Kellerer 2016-12-14 09:55:36 Re: pg_upgrade 9.0 to 9.6