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

From: Alexander Farber <alexander(dot)farber(at)gmail(dot)com>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Check constraints for varchar[] and varchar[][] columns in a table
Date: 2016-03-04 13:59:17
Message-ID: CAADeyWhnzuqexffTK4GVYWLu1Hndb7vqqO49OAaS3gBGOrtwRQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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?

Regards
Alex

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Alvaro Aguayo Garcia-Rada 2016-03-04 15:02:20 Slave-Master replication on top of BDR
Previous Message Rafal Pietrak 2016-03-04 13:30:52 Re: multiple UNIQUE indices for FK