Check constraints for varchar[] and varchar[][] columns in a table

Question

For a word game I am trying to add CHECK constraints to the VARCHAR arrays:

CREATE TABLE words_games (
        gid SERIAL PRIMARY KEY,
        created timestamptz NOT NULL,

        player1 integer REFERENCES words_users(uid) ON DELETE CASCADE NOT NULL,
        player2 integer REFERENCES words_users(uid) ON DELETE CASCADE,

        played1 timestamptz,
        played2 timestamptz,

        hand1 varchar[7] NOT NULL CHECK (ALL(hand1) ~ '^[*A-Z]$'),
        hand2 varchar[7] NOT NULL CHECK (ALL(hand2) ~ '^[*A-Z]$'),
        pile  varchar[116] NOT NULL CHECK (ALL(pile) ~ '^[*A-Z]$'),

        board varchar[15][15] NOT NULL CHECK (ALL(board) ~ '^[.A-Z]$'),
        style integer NOT NULL CHECK (1 <= style AND style <= 4)
);

but get syntax errors:

ERROR:  syntax error at or near "ALL"
LINE 8:         hand1 varchar[7] NOT NULL CHECK (ALL(hand1) ~ '^[A-Z...
                                                 ^

probably because the ALL keyword is supposed to be on the right side?

Could anyone please recommend a way to implement check constraints here?

This is a [long-standing limitation](http://www.postgresql.org/message-id/5275.1143217555@sss.pgh.pa.us). The only workaround is to create an inverse function for `LIKE`/`~`/etc. (in your case, it also needs to be immutable). — pozs, Mar 04 '16 at 15:53
Or, you can [create a domain type](http://www.postgresql.org/docs/current/static/sql-createdomain.html) (which can have its own check constraints) & use an array of that domain instead. — pozs, Mar 04 '16 at 15:57
@pozs Unfortunately, [Arrays of domains are not yet supported](http://www.postgresql.org/docs/current/static/arrays.html). — Abelisto, Mar 04 '16 at 16:00

score 2 · Accepted Answer · answered Mar 04 '16 at 15:57

Yes, it is impossible to use all() or any() functions as left comparision argument. Create function instead:

create or replace function check_array_regexp_all(text[], text) returns boolean as $$
  select bool_and(x) from (select unnest($1) ~ $2 as x) t;
$$ language sql immutable;

and use it for constraint:

...
hand1 varchar[7] NOT NULL CHECK (check_array_regexp_all(hand1, '^[*A-Z]$')),
...

Check constraints for varchar[] and varchar[][] columns in a table

1 Answers1