Search Postgresql Archives

Re: Expression of check constraint

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Dirk Mika wrote:
> if I add the following check constraint to a table:
>  
> ALTER TABLE public.times_places
>    ADD CONSTRAINT ck_tp_ratified CHECK
>           (ratified IS NULL OR (ratified IN ('Y', 'N')));
>  
> It becomes the following when describing the table in psql:
>  
> Check constraints:
>     "ck_tp_ratified" CHECK (ratified IS NULL OR (ratified::text = ANY (ARRAY['Y'::character varying, 'N'::character varying]::text[])))
>  
> The behavior of the check constraint is logically identical and this seems plausible to me, but I still wonder why:
> 1.    does the expression x in (a, b) become the expression x = any(array(a, b)?

Because that's what the PostgreSQL query parser makes out of an IN list.

> 2.    why is the array expression casted so wildly? First to character varying and then to text[]?

Because "text" is the preferred string type, and there is no "=" operator for "character varying".
But don't worry, casting "character varying" to "text" doesn't cost anything, since the
types are binary coercible (the storage ist the same).

> 3.    The column ratified is of type character varying(1).  Why is it casted to text?

See 2. above.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com






[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux