Lists: | pgsql-docs |
---|
From: | Nikolaos Ikonomopoulos <ikonomn(at)hotmail(dot)com> |
---|---|
To: | <pgsql-docs(at)postgresql(dot)org> |
Subject: | CONSTRAINT on ARRAY ELEMENTS |
Date: | 2012-11-03 09:43:06 |
Message-ID: | DUB103-W19690C266F73C74A461C7CA0660@phx.gbl |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-docs |
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.
From: | Dmitriy Igrishin <dmitigr(at)gmail(dot)com> |
---|---|
To: | Nikolaos Ikonomopoulos <ikonomn(at)hotmail(dot)com> |
Cc: | pgsql-docs(at)postgresql(dot)org |
Subject: | Re: CONSTRAINT on ARRAY ELEMENTS |
Date: | 2012-11-04 09:54:41 |
Message-ID: | CAAfz9KMYyOX=5bneYu5DJJNNWWTkAMZb3i0tVbUW-oKkYRM82w@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-docs |
Hey Nikolaos,
2012/11/3 Nikolaos Ikonomopoulos <ikonomn(at)hotmail(dot)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.
From: | Jeff Davis <pgsql(at)j-davis(dot)com> |
---|---|
To: | Nikolaos Ikonomopoulos <ikonomn(at)hotmail(dot)com> |
Cc: | pgsql-docs(at)postgresql(dot)org |
Subject: | Re: CONSTRAINT on ARRAY ELEMENTS |
Date: | 2012-11-04 17:04:00 |
Message-ID: | 1352048640.6292.1.camel@jdavis-laptop |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-docs |
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: | Nikolaos Oikonomopoulos <ikonomn(at)hotmail(dot)com> |
---|---|
To: | pgsql-docs(at)postgresql(dot)org |
Subject: | Re: CONSTRAINT on ARRAY ELEMENTS |
Date: | 2012-11-04 17:32:59 |
Message-ID: | 1352050379355-5730592.post@n5.nabble.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-docs |
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.