Conditional check constraint in Postgresql with Regex

Question

I'm trying to create a conditional check constraint in postgresql. When a_type is 'a', I want the b to only contain digit. When a_type is not 'a', I want b to contain any characters. How would I accomplish this? I have this now:

EDIT: I think this should work.

CONSTRAINT test CHECK (a_type <> 'a' AND b ~* '^.$') OR (a_type = 'a' AND b ~* '^[0-9]+$')

klin · Accepted Answer · 2017-12-02 00:23:37Z

1

You do not have to use regexp when a_type <> 'a':

check (a_type <> 'a' or a_type = 'a' and b ~* '^[0-9]+$')

answered Dec 2, 2017 at 0:23

klin

123k15 gold badges240 silver badges262 bronze badges

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

Collectives™ on Stack Overflow

Conditional check constraint in Postgresql with Regex

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related