How to use a cast function in a check constraint in PostgreSQL

Question

I want to check if the values in a column are in the range of 0 to 255, but the column's type is a char, so I'm trying to use a cast, but I get an error.

This is my code:

alter table poste add constraint chk_poste check ( cast(ad as int )ad_int  (ad_int is not null) and  ad<= 0 and ad>=255);

If you get an error, then please include that error in your question. — Mark Rotteveel, Mar 07 '21 at 19:18
A `CHECK` constraint needs to produce a boolean result. I'm guessing the issue is `cast(ad as int )ad_int ...`, that is a statement that does not produce a boolean result. — Adrian Klaver, Mar 07 '21 at 19:36

score 0 · Answer 1 · answered Mar 09 '21 at 03:28

You are attempting to alias the converted value cast(ad as int )ad_int. This is not valid. Try:

alter table poste add constraint chk_post check (ad ~ '^\d+$' and ad::int between 0 and 255);

It first checks that the initial value numeric (assuming you do not want decimals) and can be converted to the validates the range inclusive of both values.

How to use a cast function in a check constraint in PostgreSQL

1 Answers1