Answer a 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

Answers

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}$'