Set a check constraint for text array in Postgresql

Question

I'm having a table called student, with id and name as fields in PostgreSQL:

Create table student (id int, name text[]);

I need to add the constraint for the name field. Which means it has to accept only character for that field. But the field name is a text array.

I tried this check constraint:

Alter table student 
add constraint stud_const check (ALL(name) NOT LIKE '%[^a-zA-Z]%');

But it throws this error:

ERROR:  syntax error atERROR:  syntax error at or near "all"
LINE 1: ... student add constraint stud_const check (all(name) ...
 or near "all"

How could I solve this problem? The constraint should be set to whole array.

Answer 1

It is necessary to unnest the array to match it to a regular expression:

select bool_and (n ~ '^[a-zA-Z]*$')
from unnest(array['John','Mary']) a(n)
;
 bool_and 
----------
 t

bool_and. Since it is not possible to use a subquery in the check constraint wrap it in a function:

create function check_text_array_regex (
    a text[], regex text
) returns boolean as $$

    select bool_and (n ~ regex)
    from unnest(a) s(n);

$$ language sql immutable;

and use the function in the check constraint:

create table student (
    id serial,
    name text[] check (check_text_array_regex (name, '^[a-zA-Z]*$'))
);

Test it:

insert into student (name) values (array['John', 'Mary']);
INSERT 0 1

insert into student (name) values (array['John', 'Mary2']);
ERROR:  new row for relation "student" violates check constraint "student_name_check"
DETAIL:  Failing row contains (2, {John,Mary2}).