postgresql - Postgres CHAR check constraint not evaluating correctly

Question

My goal is to have a column which accepts only values of the type letters and underscores in Postgres. For some reason though I'm not managing to get Postgres to enforce the regex correctly.

Things I tried:

Produces column which won't accept strings with digits

action_type CHAR(100) check (action_type ~ '^(?:[^[:digit:]]*)$')

Produces column which won't accept anything

action_type CHAR(100) check (action_type ~ '^(?:[^[:digit:] ]*)$')

Produces column which won't accept anything

action_type CHAR(100) check (action_type ~ '^([[:alpha:]_]*)$'),

I have tried using multiple variations of the above as well as using SIMILAR TO instead of '~'. From my experience the column either accepts everything or nothing, depends on the given constraint.

I'm running this on the timescaledb docker image locally which is running PostgreSQL 12.5.

Any help would be greatly appreciated as I am at my wits end.

question from:https://stackoverflow.com/questions/65872472/postgres-char-check-constraint-not-evaluating-correctly

Answer 1

Try this:

CREATE TABLE your_table(
    action_type text
);

ALTER TABLE your_table ADD CONSTRAINT check_letters 
CHECK (action_type ~ '^[a-z\_]*$' )

This should only allow a-z characters and underscore _ for action_type column.

Also note that this is case sensitive, if you need case insensitive match, use ~* instead of ~

And also, this allows empty string, if you don't want that, use '^[a-z\_]+$' instead