Add constraint to table with check in Postgres

Question

I would like to ALTER my existing table by adding CONSTRAINT to specific column to respect some regex.

I try to normalize phone prefix number by executing:

ALTER TABLE users
    ADD CONSTRAINT users_prefix_constraint 
        CHECK (users.phone_prefix ~ '^\+\d{1,3}$');

And it's working correctly.

But I would like to use it more generic and define DOMAIN which holds regex pattern, to use the same pattern in different places (i.e. in triggers). That's why I imagine to do something like:

CREATE DOMAIN ph_prefix AS TEXT CHECK (VALUE ~'^\+\d{1,3}$');

ADD CONSTRAINT users_prefix_constraint CHECK (users.phone_prefix :: ph_prefix);

Problem here is, that CHECK part expects some function returning boolean, and casting doesn't.

Is there a way to do it?
Is that a good way to define constraint? Maybe it's not preferred way.

Laurenz Albe · Accepted Answer · 2021-02-16 13:24:27Z

2

I think you are missing the point of domains.

You would not add a domain as a constraint, but you would use it as the data type for a column:

ALTER TABLE users ALTER phone_prefix TYPE ph_prefix;

answered Feb 16, 2021 at 13:24

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

1 Comment

EnGoPy Over a year ago

Thank you. I missed the clearest solution.

Stefanov.sm · Accepted Answer · 2021-02-16 13:31:08Z

2

If you have a reason to define a domain but not use it as a data type of users.phone_prefix column then create this simple function

create or replace function ph_prefix_valid(s text) returns boolean as
$$
begin
 perform s::ph_prefix;
 return true;
exception when others then
 return false;
end;
$$ language plpgsql;

and then

ADD CONSTRAINT users_prefix_constraint CHECK (ph_prefix_valid(users.phone_prefix));

edited Feb 16, 2021 at 13:31

answered Feb 16, 2021 at 13:24

Stefanov.sm

13.2k2 gold badges24 silver badges24 bronze badges

Collectives™ on Stack Overflow

Add constraint to table with check in Postgres

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related