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

On Fri, Mar 4, 2016 at 6:59 AM, Alexander Farber <alexander(dot)farber(at)gmail(dot)com
> wrote:

> Hello,
>
> 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?
>
>
​Google'd: postgresql regexp array matching
Found:
http://stackoverflow.com/questions/22098706/how-to-use-regular-expression-with-any-array-operator

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.

David J.
​