Re: Expression of check constraint

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