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_-- From dmitigr@gmail.com Mon Jun 1 08:15:46 2026 Received: from makus.postgresql.org ([98.129.198.125]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) 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 ) 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 ; 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: References: Date: Sun, 4 Nov 2012 12:54:41 +0300 Message-ID: Subject: Re: CONSTRAINT on ARRAY ELEMENTS From: Dmitriy Igrishin To: Nikolaos Ikonomopoulos 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 > > 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,

2012/11/3 Nikolaos Ikonomop= oulos <ikonomn@hotmail.com>

CREATE TABLE employ_presence
(
=C2=A0 p_id character(6) not null,=
=C2=A0 p_month character(3) NOT NULL,
=C2=A0 statuscode integ= er array[7],
=C2=A0 CONSTRAINT unq_employ_presence UNIQUE (p_id, p_month= ),
=C2=A0 CONSTRAINT chk_employ_month CHECK (p_month =3D ANY (ARRAY['= ;Jan'::bpchar, 'Feb'::bpchar, 'Mar'::bpchar, 'Apr&#= 39;::bpchar, 'May'::bpchar, 'Jun'::bpchar, 'Jul'::b= pchar, 'Aug'::bpchar, 'Sep'::bpchar, 'Oct'::bpchar,= 'Nov'::bpchar, 'Dec'::bpchar]))
);

How can add a CONSTRAINT on statuscode array eleme= nts to accept values between 0 to 5

0 =3D Employ present
1 =3D Em= ploy Patient
2 =3D day off
3 =3D Regular vacation
4= =3D external job assignment
5 =3D external job assignment abroad
6 to 9=C2=A0 for future use

= Thanks.

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

--
// Dmitriy.

--14dae9340cc192fd5e04cda85b89-- From pgsql@j-davis.com Mon Jun 1 08:15:46 2026 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TV3cF-0005gt-Ul for pgsql-docs@postgresql.org; Sun, 04 Nov 2012 17:04:08 +0000 Received: from mail-da0-f46.google.com ([209.85.210.46]) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TV3cD-0002WC-2M for pgsql-docs@postgresql.org; Sun, 04 Nov 2012 17:04:07 +0000 Received: by mail-da0-f46.google.com with SMTP id n41so2343111dak.19 for ; Sun, 04 Nov 2012 09:04:02 -0800 (PST) X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=google.com; s=20120113; h=message-id:subject:from:to:cc:date:in-reply-to:references :organization:content-type:x-mailer:content-transfer-encoding :mime-version:x-gm-message-state; bh=5bPMnJbHBHAMpOj08sOS6xGHsY4nLx27woVhRCrQdB8=; b=OAa2AWwvC49wmedKOOFTqEO8DqyqcIh691UYJ9LaOsbnXqagyZzG2hcPj7/sx496tk 4Ki8O2b6u72NxQRe1RO3sG8ZVxBaql0LTbhxFNXej6JwuQq0DfROPMsbFCi5/XeNQadZ /8ynD7dtXwo6LTEKuu2z2lBKeJuzpNPmjNCuk/f1RbXGV4QqqOuAFVFDDSTtmBrmplu2 irfgiIznPcip5cvh2c7lWX4KKiMs5uXPJRPsxde3hQIxJqN12wKHfcQ4NgFrhhmK+zJw Dqyyhi6s2PW88HN8g+L2KJQ46N9fx/ar2rrTyY03lScrsKEEzpteHNqmnkXZnEBHkgUG tzOg== Received: by 10.68.232.2 with SMTP id tk2mr23769257pbc.92.1352048642681; Sun, 04 Nov 2012 09:04:02 -0800 (PST) Received: from [192.168.1.102] (c-69-181-249-16.hsd1.ca.comcast.net. [69.181.249.16]) by mx.google.com with ESMTPS id v2sm7459905paz.27.2012.11.04.09.04.01 (version=SSLv3 cipher=OTHER); Sun, 04 Nov 2012 09:04:02 -0800 (PST) Message-ID: <1352048640.6292.1.camel@jdavis-laptop> Subject: Re: CONSTRAINT on ARRAY ELEMENTS From: Jeff Davis To: Nikolaos Ikonomopoulos Cc: pgsql-docs@postgresql.org Date: Sun, 04 Nov 2012 09:04:00 -0800 In-Reply-To: References: Organization: Content-Type: text/plain; charset="UTF-8" X-Mailer: Evolution 3.2.3-0ubuntu6 Content-Transfer-Encoding: 7bit Mime-Version: 1.0 X-Gm-Message-State: ALoCoQnfGBecswWVmHykv3VM3IEYTwRgJ283IEG2ZIwCrYA2ntOVgERC3KILBJ1R/IeotyDGdBU6 X-Pg-Spam-Score: -2.6 (--) X-Archive-Number: 201211/4 X-Sequence-Number: 7496 On Sat, 2012-11-03 at 09:43 +0000, Nikolaos Ikonomopoulos wrote: > > 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 You can try: CHECK (statuscode <@ ARRAY[1,2,3,4,5]) Regards, Jeff Davis From ikonomn@hotmail.com Mon Jun 1 08:15:46 2026 Received: from magus.postgresql.org ([87.238.57.229]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TV44H-0001BL-8P for pgsql-docs@postgresql.org; Sun, 04 Nov 2012 17:33:05 +0000 Received: from sam.nabble.com ([216.139.236.26]) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TV44E-0002wg-UG for pgsql-docs@postgresql.org; Sun, 04 Nov 2012 17:33:04 +0000 Received: from [192.168.236.26] (helo=sam.nabble.com) by sam.nabble.com with esmtp (Exim 4.72) (envelope-from ) id 1TV44B-0004Ws-HK for pgsql-docs@postgresql.org; Sun, 04 Nov 2012 09:32:59 -0800 Date: Sun, 4 Nov 2012 09:32:59 -0800 (PST) From: Nikolaos Oikonomopoulos To: pgsql-docs@postgresql.org Message-ID: <1352050379355-5730592.post@n5.nabble.com> In-Reply-To: <1352048640.6292.1.camel@jdavis-laptop> References: <1352048640.6292.1.camel@jdavis-laptop> Subject: Re: CONSTRAINT on ARRAY ELEMENTS MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Pg-Spam-Score: -0.1 (/) X-Archive-Number: 201211/5 X-Sequence-Number: 7497 I tried your solution works fine Regards, N.Oikonomopoulos. -- View this message in context: http://postgresql.1045698.n5.nabble.com/CONSTRAINT-on-ARRAY-ELEMENTS-tp5730559p5730592.html Sent from the PostgreSQL - docs mailing list archive at Nabble.com.