PostgreSQL digit matching regex strange behavior

Question

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.

Community · Accepted Answer · 2017-05-23 11:48:21Z

1

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)

edited May 23, 2017 at 11:48

CommunityBot

11 silver badge

answered Oct 7, 2016 at 17:05

klin

123k15 gold badges241 silver badges263 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

D. Dimitrić Over a year ago

Using character varying (5) column type works! But, does it come with a performance hit? Thanks a bunch :)

klin Over a year ago

varchar(5) is better, note however that you've already limited the length of string using check. On a side note I'm using solely text. In the quoted post you can find some benchmarks as well.

Collectives™ on Stack Overflow

PostgreSQL digit matching regex strange behavior

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related