Received: from magus.postgresql.org ([87.238.57.229]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TUaG4-0004Zb-1u for pgsql-docs@postgresql.org; Sat, 03 Nov 2012 09:43:16 +0000 Received: from dub0-omc1-s15.dub0.hotmail.com ([157.55.0.214]) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TUaFw-0000aP-Oe for pgsql-docs@postgresql.org; Sat, 03 Nov 2012 09:43:15 +0000 Received: from DUB103-W19 ([157.55.0.237]) by dub0-omc1-s15.dub0.hotmail.com with Microsoft SMTPSVC(6.0.3790.4675); Sat, 3 Nov 2012 02:43:06 -0700 Message-ID: Content-Type: multipart/alternative; boundary="_d39f97b4-1665-4404-be55-ab7220ab2847_" X-Originating-IP: [94.68.228.141] From: Nikolaos Ikonomopoulos To: Subject: CONSTRAINT on ARRAY ELEMENTS Date: Sat, 3 Nov 2012 09:43:06 +0000 Importance: Normal MIME-Version: 1.0 X-OriginalArrivalTime: 03 Nov 2012 09:43:06.0379 (UTC) FILETIME=[A04E85B0:01CDB9A7] X-Pg-Spam-Score: -0.6 (/) X-Archive-Number: 201211/2 X-Sequence-Number: 7494 --_d39f97b4-1665-4404-be55-ab7220ab2847_ Content-Type: text/plain; charset="iso-8859-7" Content-Transfer-Encoding: quoted-printable CREATE TABLE employ_presence ( p_id character(6) not null=2C p_month character(3) NOT NULL=2C statuscode integer array[7]=2C CONSTRAINT unq_employ_presence UNIQUE (p_id=2C p_month)=2C CONSTRAINT chk_employ_month CHECK (p_month =3D ANY (ARRAY['Jan'::bpchar= =2C 'Feb'::bpchar=2C 'Mar'::bpchar=2C 'Apr'::bpchar=2C 'May'::bpchar=2C 'Ju= n'::bpchar=2C 'Jul'::bpchar=2C 'Aug'::bpchar=2C 'Sep'::bpchar=2C 'Oct'::bpc= har=2C 'Nov'::bpchar=2C 'Dec'::bpchar])) )=3B How can add a CONSTRAINT on statuscode array elements to accept values betw= een 0 to 5 0 =3D Employ present 1 =3D Employ Patient 2 =3D day off=20 3 =3D Regular vacation 4 =3D external job assignment=20 5 =3D external job assignment abroad 6 to 9 for future use Thanks. = --_d39f97b4-1665-4404-be55-ab7220ab2847_ Content-Type: text/html; charset="iso-8859-7" Content-Transfer-Encoding: quoted-printable

CREATE TABLE employ_presence
(
=3B p_id character(6) not nul= l=2C
=3B p_month character(3) NOT NULL=2C
=3B statuscode= integer array[7]=2C
=3B CONSTRAINT unq_employ_presence UNIQUE = (p_id=2C p_month)=2C
=3B CONSTRAINT chk_employ_month CHECK (p_month= =3D ANY (ARRAY['Jan'::bpchar=2C 'Feb'::bpchar=2C 'Mar'::bpchar=2C 'Apr'::b= pchar=2C 'May'::bpchar=2C 'Jun'::bpchar=2C 'Jul'::bpchar=2C 'Aug'::bpchar= =2C 'Sep'::bpchar=2C 'Oct'::bpchar=2C 'Nov'::bpchar=2C 'Dec'::bpchar]))
= )=3B

How can add a CONSTRAINT on statuscode array ele= ments to accept values between 0 to 5

0 =3D Employ present
1 =3D = Employ Patient
2 =3D day off
3 =3D Regula= r vacation
4 =3D external job assignment
5 =3D external job assignme= nt abroad
6 to 9 =3B for future use

Thanks.
= --_d39f97b4-1665-4404-be55-ab7220ab2847_--