PostgreSQL "invalid regular expression: invalid escape \ sequence" when using Regex constraint

Question

This is my SQL code:

CREATE TABLE country (
    id      serial          NOT NULL PRIMARY KEY,
    name    varchar(100)    NOT NULL CHECK(name ~ '^[-\p{L} ]{2,100}$'),
    code    varchar(3)      NOT NULL
);

Notice the regex constraint at the name attribute. The code above will result in ERROR: invalid regular expression: invalid escape \ sequence.

I tried using escape CHECK(name ~ E'^[-\\p{L} ]{2,100}$') but again resulted in ERROR: invalid regular expression: invalid escape \ sequence.

I am also aware that if I do CHECK(name ~ '^[-\\p{L} ]{2,100}$'), or CHECK(name ~ E'^[-\p{L} ]{2,100}$'), - the SQL will receive wrong Regex and therefore will throw a constraint violation when inserting valid data.

Does PostgreSQL regex constraints not support regex patterns (\p) or something like that?

Edit #1

The Regex ^[-\p{L} ]{2,100}$ is basically allows country name that are between 2-100 characters and the allowed characters are hyphen, white-space and all letters (including latin letters).

NOTE: The SQL runs perfectly fine during the table creation but will throw the error when inserting valid data.

Additional Note: I am using PostgreSQL 12.1

Edit the question and explain what the pattern is supposed to match. — sticky bit
– sticky bit, Commented Apr 14, 2020 at 9:57
\p{L} matches any letter, you may get the same behavior using a POSIX character class, [:alpha:], try '^[-[:alpha:] ]{2,100}$' — Wiktor Stribiżew
– Wiktor Stribiżew, Commented Apr 14, 2020 at 10:03
postgresql.org/docs/current/… XQuery character class elements using \p{UnicodeProperty} or the inverse \P{UnicodeProperty} are not supported.. I read: "no - it is not supported". — madflow
– madflow, Commented Apr 14, 2020 at 10:17

Wiktor Stribiżew · Accepted Answer · 2020-04-14 10:20:39Z

4

The \p{L} Unicode category (property) class matches any letter, but it is not supported in PostgreSQL regex.

You may get the same behavior using a [:alpha:] POSIX character class

'^[-[:alpha:] ]{2,100}$'

answered Apr 14, 2020 at 10:20

Wiktor Stribiżew

631k41 gold badges502 silver badges632 bronze badges

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

2 Comments

Programer Beginner Over a year ago

I thought that [:alpha:] is equivalent to [a-zA-Z]? Even on regex101 it does not work, yet it works in the PostgreSQL database. How?

Wiktor Stribiżew Over a year ago

@ProgramerBeginner So, you already knew about the POSIX character class, good. POSIX character classes are locale-aware in almost all engines where they are used, at least it is true about POSIX compliant regexps. In PostgreSQL, POSIX character classes behave as in POSIX compliant engines, it matches more than just [A-Za-z]. As for testing POSIX character classes at regex101.com, you just need to know how to do it. Watch out, regex101.com does not support POSIX regex testing, only JS, PCRE, Python re and Go regexp (RE2).

Collectives™ on Stack Overflow

PostgreSQL "invalid regular expression: invalid escape \ sequence" when using Regex constraint

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related