CODEWITHSUNDEEP
regexpostgresqlconstraints
D. Dimitrić
D. Dimitrić

Reputation: 143

PostgreSQL digit matching regex strange behavior

So I'm having this problem where a PostgreSQL regular expression doesn't behave the same way in two different contexts - as a CONSTRAINT and with a regex_matches() function.

I want the regex to work as it does demonstrated with SELECT statements below but as a table CONSTRAINT, which for some reason it doesn't.

Has anyone else experienced this kind of behavior or does anyone have any insight on this?

Thanks!

CREATE TABLE ExampleTable (
    ID serial,
    Length char(5) NOT NULL,

    CONSTRAINT proper_formatting CHECK (Length ~* '\A[0-5]{0,1}\d{1}:[0-5]{1}\d{1}\Z') 
);

INSERT INTO ExampleTable (Length) VALUES ('03:33'); -- Passes.
INSERT INTO ExampleTable (Length) VALUES ('3:33');  -- Fails.

DROP TABLE ExampleTable;

-- In this context, it works just fine:
SELECT regexp_matches('03:33',  '\A[0-5]{0,1}\d{1}:[0-5]{1}\d{1}\Z'); -- Passes.
SELECT regexp_matches('3:33',   '\A[0-5]{0,1}\d{1}:[0-5]{1}\d{1}\Z'); -- Passes.
SELECT regexp_matches('93:33',  '\A[0-5]{0,1}\d{1}:[0-5]{1}\d{1}\Z'); -- Fails.
SELECT regexp_matches('531:33', '\A[0-5]{0,1}\d{1}:[0-5]{1}\d{1}\Z'); -- Fails.
SELECT regexp_matches('3:83',   '\A[0-5]{0,1}\d{1}:[0-5]{1}\d{1}\Z'); -- Fails.

Upvotes: 2

Views: 243

Answers (1)

klin
klin

Reputation: 121624

Do not use the type char(n):

with data(val) as (
values
    ('03:33'::char(5)),
    ('3:33'::char(5)),
    ('3:33')
)
select format('==%s==', val), val ~* '\A[0-5]{0,1}\d{1}:[0-5]{1}\d{1}\Z' matches
from data

  format   | matches 
-----------+---------
 ==03:33== | t
 ==3:33 == | f
 ==3:33==  | t
(3 rows)

See also: PostgreSQL: Difference between text and varchar (character varying)

Upvotes: 1

Related Questions