Allow constraints on array-members

ChatGPT-suggestion:

Yes, you can set up constraints on the strings inside of the array using PostgreSQL.

Unfortunately, you cannot define the constraint directly in the CREATE TABLE statement like you did in your example. Instead, you need to use a custom function and a CHECK constraint.

First, create a custom function that checks the length of the elements inside the array:

CREATE OR REPLACE FUNCTION check_array_elements_length(arr VARCHAR(20)[], max_length INTEGER)
RETURNS BOOLEAN AS $$
DECLARE
  elem VARCHAR(20);
BEGIN
  IF array_length(arr, 1) IS NULL THEN
    RETURN TRUE;
  END IF;

  FOREACH elem IN ARRAY arr
  LOOP
    IF LENGTH(elem) > max_length THEN
      RETURN FALSE;
    END IF;
  END LOOP;

  RETURN TRUE;
END;
$$ LANGUAGE plpgsql;

Then, create your table with the CHECK constraint that uses the custom function:

CREATE TABLE myName (
  myArray VARCHAR(20)[] NOT NULL CHECK (check_array_elements_length(myArray, 20))
);

This constraint ensures that each element in the myArray column has a length of no more than 20 characters.

NielsCo / typespec-postgres

Allow constraints on array-members #14