CODEWITHSUNDEEP
sqlregexpostgresqlconstraints
khafka
khafka

Reputation: 75

Regexp constraint rejecting apparently valid data in Postgresql

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?

Upvotes: 1

Views: 338

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269933

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);

Upvotes: 1

Related Questions