2

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.
D. Dimitrić
  • 133
  • 8

1 Answers1

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)

Community
  • 1
  • 1
klin
  • 112,967
  • 15
  • 204
  • 232
  • Using character varying (5) column type works! But, does it come with a performance hit? Thanks a bunch :) – D. Dimitrić Oct 07 '16 at 18:13
  • `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. – klin Oct 07 '16 at 19:38