Received: from makus.postgresql.org ([98.129.198.125])
	by malur.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <dmitigr@gmail.com>) id 1TUwui-0004rU-GH
	for pgsql-docs@postgresql.org; Sun, 04 Nov 2012 09:54:44 +0000
Received: from mail-ie0-f174.google.com ([209.85.223.174])
	by makus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <dmitigr@gmail.com>) id 1TUwug-0000iq-5U
	for pgsql-docs@postgresql.org; Sun, 04 Nov 2012 09:54:43 +0000
Received: by mail-ie0-f174.google.com with SMTP id k13so6751112iea.19
	for <pgsql-docs@postgresql.org>; Sun, 04 Nov 2012 01:54:41 -0800 (PST)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type;
	bh=rT9Aw2HG3/6H0ujwlyKJm8J8GCNb5brGr7bXz6fXvcc=;
	b=gzufwPh/EHTDuBc+Gq6kCrEFhNW/SABYc1W421yXEUaLiQANqW8WUShHk0Ehf0dWrm
	jHsOlecnWE4kzc3yEQ/TSzFcG+1tIAM7amNugItzMtPcMQ0QnGExwbYDWaFKTA45WgmE
	eP7DFZ5w98WAJqw8IPh4WlXHb13rjr9E5+XEadL7zNeMSMXu5rqB2vBg+Qhq6RDLFOeh
	dEhn8ncrrK2u1aRx8dKpBjk+dCX48OpE9OdXKWzLpL4xJLYHSXf8clA3D8AELj/0Tq3l
	i/uoI34cLoLrl2GqRpKm4LR8DoAH3wNhA79j3APOv+fMRY4e/K93Oe+SiS2MMjVgiWeo
	wvzA==
MIME-Version: 1.0
Received: by 10.50.46.199 with SMTP id x7mr6563363igm.19.1352022881401; Sun,
	04 Nov 2012 01:54:41 -0800 (PST)
Received: by 10.43.15.129 with HTTP; Sun, 4 Nov 2012 01:54:41 -0800 (PST)
In-Reply-To: <DUB103-W19690C266F73C74A461C7CA0660@phx.gbl>
References: <DUB103-W19690C266F73C74A461C7CA0660@phx.gbl>
Date: Sun, 4 Nov 2012 12:54:41 +0300
Message-ID: 
 <CAAfz9KMYyOX=5bneYu5DJJNNWWTkAMZb3i0tVbUW-oKkYRM82w@mail.gmail.com>
Subject: Re: CONSTRAINT on ARRAY ELEMENTS
From: Dmitriy Igrishin <dmitigr@gmail.com>
To: Nikolaos Ikonomopoulos <ikonomn@hotmail.com>
Cc: pgsql-docs@postgresql.org
Content-Type: multipart/alternative; boundary=14dae9340cc192fd5e04cda85b89
X-Pg-Spam-Score: -2.6 (--)
X-Archive-Number: 201211/3
X-Sequence-Number: 7495

--14dae9340cc192fd5e04cda85b89
Content-Type: text/plain; charset=UTF-8

Hey Nikolaos,

2012/11/3 Nikolaos Ikonomopoulos <ikonomn@hotmail.com>

>
> CREATE TABLE employ_presence
> (
>   p_id character(6) not null,
>   p_month character(3) NOT NULL,
>   *statuscode* integer array[7],
>   CONSTRAINT unq_employ_presence UNIQUE (p_id, p_month),
>   CONSTRAINT chk_employ_month CHECK (p_month = ANY (ARRAY['Jan'::bpchar,
> 'Feb'::bpchar, 'Mar'::bpchar, 'Apr'::bpchar, 'May'::bpchar, 'Jun'::bpchar,
> 'Jul'::bpchar, 'Aug'::bpchar, 'Sep'::bpchar, 'Oct'::bpchar, 'Nov'::bpchar,
> 'Dec'::bpchar]))
> );
>
>
>
> How can add a CONSTRAINT on *statuscode* array elements to accept values
> between 0 to 5
>
> 0 = Employ present
> 1 = Employ Patient
> 2 = day off
> 3 = Regular vacation
> 4 = external job assignment
> 5 = external job assignment abroad
> 6 to 9  for future use
>
> Thanks.
>

I recommend you to create the table "status" and create
foreign key constraint in the table "employ_presence"
instead. This will do exactly what are you want.

-- 
// Dmitriy.

--14dae9340cc192fd5e04cda85b89
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

Hey Nikolaos,<br><br><div class=3D"gmail_quote">2012/11/3 Nikolaos Ikonomop=
oulos <span dir=3D"ltr">&lt;<a href=3D"mailto:ikonomn@hotmail.com" target=
=3D"_blank">ikonomn@hotmail.com</a>&gt;</span><br><blockquote class=3D"gmai=
l_quote" style=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left=
:1ex">



<div><div dir=3D"ltr">
<br>CREATE TABLE employ_presence<br>(<br>=C2=A0 p_id character(6) not null,=
<br>=C2=A0 p_month character(3) NOT NULL,<br>=C2=A0 <b>statuscode</b> integ=
er array[7],<br>=C2=A0 CONSTRAINT unq_employ_presence UNIQUE (p_id, p_month=
),<br>=C2=A0 CONSTRAINT chk_employ_month CHECK (p_month =3D ANY (ARRAY[&#39=
;Jan&#39;::bpchar, &#39;Feb&#39;::bpchar, &#39;Mar&#39;::bpchar, &#39;Apr&#=
39;::bpchar, &#39;May&#39;::bpchar, &#39;Jun&#39;::bpchar, &#39;Jul&#39;::b=
pchar, &#39;Aug&#39;::bpchar, &#39;Sep&#39;::bpchar, &#39;Oct&#39;::bpchar,=
 &#39;Nov&#39;::bpchar, &#39;Dec&#39;::bpchar]))<br>
);<br><br><br><br>How can add a CONSTRAINT on <b>statuscode</b> array eleme=
nts to accept values between 0 to 5<br><br>0 =3D Employ present<br>1 =3D Em=
ploy <span> Patient</span><br>2 =3D day off <br>3 =3D Regular vacation<br>4=
 =3D external job assignment <br>
5 =3D external job assignment abroad<br>6 to 9=C2=A0 for future use<br><br>=
Thanks.<br> 		 	   		  </div></div>
</blockquote></div><br>I recommend you to create the table &quot;status&quo=
t; and create<br>foreign key constraint in the table &quot;employ_presence&=
quot;<br>instead. This will do exactly what are you want.<br clear=3D"all">
<br>-- <br>// Dmitriy.<br><br><br>

--14dae9340cc192fd5e04cda85b89--