Postrgesql - check constraint on table column defined as array

Question

I want to set a validation limit on the following postgres table:

 CREATE TABLE foo
(
     id int Primary Key,
     names           varchar(40)[]  
);

Since names are an array, it is difficult for me to define a check for each element of the array. The following limitation is my best guess (doesn't work):

ALTER TABLE foo
    ADD CONSTRAINT check_names
        CHECK (ALL(names[]) ~* '^[A-Z]')
;

Basically, each element of names [] should consist only of capital letters.

Answer 1

As pozs commented on your post, you cannot put a restriction CHECKlike this in an array as far as I know (a real professional can fix me here).

, BEFORE INSERT, names ​​. , a CONSTRAINT. , .

CREATE FUNCTION all_caps_array_only() RETURNS trigger AS $$
DECLARE
  name varchar(40);
BEGIN
  FOREACH name IN ARRAY NEW.names LOOP
    IF name !~ '[A-Z]+' THEN
      RETURN NULL; -- Fail the INSERT
    END IF;
  END LOOP;
  RETURN NEW; -- Make the INSERT happen
END;
$$ LANGUAGE plpgsql;

Answer 2

:

CREATE TABLE foo(
     id int Primary Key,
     names           varchar(40)[]
);

ALTER TABLE foo
  ADD CONSTRAINT check_names
    CHECK (length(regexp_replace(array_to_string(names, ''),'[A-Z]*',''))=0);

INSERT INTO foo (id, names) VALUES (4, array ['','3']);
ERROR:  new row for relation "foo" violates check constraint "check_names"
DETAIL:  Failing row contains (4, {"",3}).