Re: Expression of check constraint

Hello,

On Thu, 2019-07-04 at 05:58 +0000, Dirk Mika wrote:
> Hi,
>
> 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)?
> 2. why is the array expression casted so wildly? First to
> character varying and then to text[]?
> 3. The column ratified is of type character varying(1). Why is it
> casted to text?
>
> Dirk
> --
> Dirk Mika
> Software Developer
>
>

Why don't you define "ratified" as CHAR(1)?

AFAIK, constraint evaluation is based upon the column's underlying data
type.

Cheers,
Robert