On Fri, Mar 4, 2016 at 6:59 AM, Alexander Farber <alexander.far...@gmail.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.
