Re: BUG #17352: Column CHECK regex error

On Mon, 3 Jan 2022 at 11:50, PG Bug reporting form
<noreply@postgresql.org> wrote:

> select '01' ~ '^[\d-]{2,8}$'  -> true,

This is matching '01':text ( ~ is documented only for text ).
..
>     code character(8) COLLATE pg_catalog."default" NOT NULL
>       CONSTRAINT "Only digits" CHECK (code ~ '^[\d-]{2,8}$'),
...
>   'p977main' , '01' , 'Tables def' , DEFAULT , '' , ''

These well be matching '01'::char(8)::text

> If we drop CONSTRAINT "Only digits" that insert will be done properly.
> If we change  column into character varying(8) and left CONSTRAINT "Only
> digits" that insert will be done without error.

Intrigued by this I did ( on 12 which was handy, YMMV )

s=> select '01'::char(8)::text ~ '^\d+$', '01'::char(8) ~ '^\d+$',
'01'::char(8) ~ '^\d+\s+$';
 ?column? | ?column? | ?column?
----------+----------+----------
 t        | f        | t

It looks like when matching against char some optimization kicks in
and avoids the intermediate cast to text which will trim trailing
spaces.

Going to pg_catalog I see 3 versions of ~ in pg_operator which seem
text related, and there it goes beyond my knowledge. Do not know how
to fix, just posting it in case it helps someone more experienced.

Francisco Olarte.

PS: You could probably make it work by adding \s* or casting, but it
seemed you already knew that workaround.

FO.