Regexp constraint rejecting apparently valid data in Postgresql

Question

Using Postgres 10.3 with pgAdmin 4 v3.0

I have a constraint for GB national grid references on a column gridref of a table my_table:

ALTER TABLE my_table
ADD CONSTRAINT my_table_gridref_check 
CHECK (gridref::text 
~~ '[A-Z][A-Z][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'::text);

I have a grid reference which appears to match that format (AB12345678 - ie 2 alpha, 8 numeric - not a real grid reference, but a valid pattern), but on trying to insert a record with that value, the insert fails on that constraint, with this error

ERROR: new row for relation "my_table" violates check constraint "my_table_gridref_check"

However, if I do

SELECT select regexp_matches('AB12345678',
'[A-Z][A-Z][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')

then I get as a result the single-item set containing that grid ref.

All of which seems to indicate that the grid ref should be accepted (which is what I expected), so I don't understand why the constraint is triggering.

Any ideas what I'm doing wrong? Are there restrictions or gotchas on the use of regular expressions as constraints in Postgres?

Gordon Linoff · Accepted Answer · 2018-08-21 14:49:59Z

1

I had to look this up in the documentation:

The operator ~~ is equivalent to LIKE, and ~~* corresponds to ILIKE.

Postgres does not support character ranges in LIKE.

You want ~ (or regexp_match()):

CHECK (gridref::text ~ '[A-Z][A-Z][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'::text);

I'm guessing you really want:

CHECK (gridref::text ~ '^[A-Z][A-Z][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]$'::text);

Note the anchors so this matches the entire string.

Or more simply:

CHECK (gridref::text ~ '^[A-Z]{2}[0-9]{8}$'::text);

answered Aug 21, 2018 at 14:49

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

1 Comment

khafka Over a year ago

Fabulous, that's exactly what I needed. The constraint was translated by pgAdmin into pg format, so I didn't catch the nuance between "~" and "~~". Many thinks

Collectives™ on Stack Overflow

Regexp constraint rejecting apparently valid data in Postgresql

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related