Postgres Pro
Downloads
Home   >   mailing lists

Thread: BUG #17352: Column CHECK regex error

BUG #17352: Column CHECK regex error

From
PG Bug reporting form
Date:
The following bug has been logged on the website:

Bug reference:      17352
Logged by:          Serge Salamatin
Email address:      salamsp@gmail.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}$'),
    txt citext COLLATE pg_catalog."default" NOT NULL,
    ui_id bigint,
    action citext COLLATE pg_catalog."default",
    tip citext COLLATE pg_catalog."default",
    CONSTRAINT mnu_pkey PRIMARY KEY (mnunm, code, txt)
);

INSERT INTO public.mnu (
mnunm , code , txt , ui_id , action , tip 
  )
  OVERRIDING USER VALUE VALUES(
  'p977main' , '01' , 'Tables def' , DEFAULT , '' , '' 
  );

returns  ->

"ERROR:  new row for relation "mnu" violates check constraint "Only
digits"
DETAIL:  Failing row contains (p977main  , 01      , Tables def, null, ,
).
SQL state: 23514"

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.

Re: BUG #17352: Column CHECK regex error

From
Francisco Olarte
Date:
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.

Re: BUG #17352: Column CHECK regex error

From
Vik Fearing
Date:
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@gmail.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