"CHECK" constraint using `IN` -> why a double cast?-CodePudding

When I specify an "CHECK" constraint like:

CONSTRAINT ADR_CHECK_1 CHECK (ACTIVE IN('Y', 'N'))

Then PostgreSQL implements this as:

CONSTRAINT adr_check_1 CHECK (active::text = ANY (ARRAY['Y'::character varying::text, 'N'::character varying::text]))

-- or, also seen --

CONSTRAINT adr_check_1 CHECK (active::text = ANY (ARRAY['Y'::character varying, 'N'::character varying]::text[]))

Why this double cast? (first to character varying, and then to text)

CodePudding user response：

That must happen during parsing.

Initially, string literals like 'Y' are of type unknown, then the type gets resolved by context. Since the string literal is compared to a column of type character varying, 'Y' is resolved as type character varying. This must be the reason for the first cast. (Note: I didn't read the source to verify that.)

IN is translated to = ANY, so the data type resolution rules for operators kick in. Since there is no operator = for type character varying, both sides are cast to the preferred character type text. This is the reason for the second cast.

Perhaps something could be improved in PostgreSQL to avoid that oddity, but you can easily avoid it if you define the constraint like this:

ALTER TABLE adr ADD CHECK (active = ANY (ARRAY['Y', 'N']));

or, equivaletly,

ALTER TABLE adr ADD CHECK (active::text IN ('Y', 'N'));