Re: BUG #17352: Column CHECK regex error

On 1/3/22 11:38 AM, PG Bug reporting form wrote:
> The following bug has been logged on the website:
>
> Bug reference: 17352
> Logged by: Serge Salamatin
> Email address: salamsp(at)gmail(dot)com
> PostgreSQL version: 13.5
> Operating system: Ubuntu 13.5-2.pgdg20.04+1
> Description:
>
> select '01' ~ '^[\d-]{2,8}$' -> true,
>
> but in next
>
> CREATE TABLE IF NOT EXISTS public.mnu
> (
> mnunm character(10) COLLATE pg_catalog."default" NOT NULL,
> code character(8) COLLATE pg_catalog."default" NOT NULL
> CONSTRAINT "Only digits" CHECK (code ~ '^[\d-]{2,8}$'),

The bug here is that you are using the character(n) type, which you
shouldn't. That type has archaic (but standard) behavior that pads the
value with spaces. Your regex does not account for that.

Don't use that type. Use text instead.

https://wiki.postgresql.org/wiki/Don%27t_Do_This#Don.27t_use_char.28n.29

--
Vik Fearing