Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Postrgesql - check constraint on table column defined as array

Tags:

postgresql

I want to set a check constraint on the following postgres table:

 CREATE TABLE foo
(
     id int Primary Key,
     names           varchar(40)[]  
);

Since names is an array, I have trouble defining a check on each element of the array. The following constraint is my best guess (not working) :

ALTER TABLE foo
    ADD CONSTRAINT check_names
        CHECK (ALL(names[]) ~* '^[A-Z]')
;

Basically each element of names[] should be made of only capital letters.

like image 441
Sven Avatar asked Jan 19 '16 10:01

Sven


1 Answers

Its doable even without a separate Trigger Function:

CREATE TABLE foo(
     id int Primary Key,
     names           varchar(40)[]
);

ALTER TABLE foo
  ADD CONSTRAINT check_names
    CHECK (length(regexp_replace(array_to_string(names, ''),'[A-Z]*',''))=0);

INSERT INTO foo (id, names) VALUES (4, array ['','3']);
ERROR:  new row for relation "foo" violates check constraint "check_names"
DETAIL:  Failing row contains (4, {"",3}).
like image 73
Robins Tharakan Avatar answered Sep 19 '22 12:09

Robins Tharakan