Thread: 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
 

Hi


--
Dirk Mika
Software Developer

mika:timing GmbH
Strundepark - Kürtener Str. 11b
51465 Bergisch Gladbach
Germany

fon +49 2202 2401-1197
dirk.mika@mikatiming.de
www.mikatiming.de

AG Köln HRB 47509 * WEEE-Reg.-Nr. DE 90029884
Geschäftsführer: Harald Mika, Jörg Mika



## How2Use
## the ChampionChip by mika:timing
## https://youtu.be/qfOFXrpSKLQ
Am 04.07.19, 10:50 schrieb "rob stone" <floriparob@gmail.com>:

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

This will change the constraint to

Check constraints:
    "ck_tp_ratified" CHECK (ratified IS NULL OR (ratified = ANY (ARRAY['Y'::bpchar, 'N'::bpchar])))

Less casting. (
Does the casting from one "String" type to another "String" type have any performance impact, btw?

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

But isn't char and varchar all stored in the same data structure?

Dirk

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

Dirk Mika <Dirk.Mika@mikatiming.de> writes:
> 3.    The column ratified is of type character varying(1).  Why is it casted to text?

Type varchar in Postgres is a poor stepchild without any operators
of its own.  Text is the "native" string type and any comparison etc
will require casting varchar to text first.  It happens that the
expression decompilation code will show you those implicit casts
explicitly, but they don't really mean much.

            regards, tom lane