for a Scrabble-like word game using PostgreSQL 9.5 as backend 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 near "ALL"
ERROR: syntax error at or near "ALL" LINE 8: hand1 varchar[7] NOT NULL CHECK (ALL(hand1) ~ '^[A-Z... ^ - probably because that keyword is supposed to be on the right side?
Could anyone please recommend a way to implement check constraints here?
The custom operator is the most direct solution - and meets this need quite well - the array-taking function is overkill for this though has the benefit of being more explicit and it can expanded upon to do things a simple operator cannot.
