CODEWITHSUNDEEP
databasepostgresqldatabase-designconstraints
EnGoPy
EnGoPy

Reputation: 353

Add constraint to table with check in Postgres

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.

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

Upvotes: 1

Views: 828

Answers (2)

Stefanov.sm
Stefanov.sm

Reputation: 13049

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));

Upvotes: 2

Laurenz Albe
Laurenz Albe

Reputation: 246523

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;

Upvotes: 2

Related Questions