pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #17352: Column CHECK regex error
3+ messages / 3 participants
[nested] [flat]
* BUG #17352: Column CHECK regex error
@ 2022-01-03 10:38 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 2 replies; 3+ messages in thread
From: PG Bug reporting form @ 2022-01-03 10:38 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: salamsp@gmail.com
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #17352: Column CHECK regex error
@ 2022-01-03 11:11 Francisco Olarte <folarte@peoplecall.com>
parent: PG Bug reporting form <noreply@postgresql.org>
1 sibling, 0 replies; 3+ messages in thread
From: Francisco Olarte @ 2022-01-03 11:11 UTC (permalink / raw)
To: salamsp@gmail.com; pgsql-bugs@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #17352: Column CHECK regex error
@ 2022-01-03 11:16 Vik Fearing <vik@postgresfriends.org>
parent: PG Bug reporting form <noreply@postgresql.org>
1 sibling, 0 replies; 3+ messages in thread
From: Vik Fearing @ 2022-01-03 11:16 UTC (permalink / raw)
To: salamsp@gmail.com; pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2022-01-03 11:16 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-01-03 10:38 BUG #17352: Column CHECK regex error PG Bug reporting form <noreply@postgresql.org>
2022-01-03 11:11 ` Francisco Olarte <folarte@peoplecall.com>
2022-01-03 11:16 ` Vik Fearing <vik@postgresfriends.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox