Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

[DOCS] CONSTRAINT on ARRAY ELEMENTS

2 views
Skip to first unread message

Nikolaos Ikonomopoulos

unread,
Nov 3, 2012, 5:43:06 AM11/3/12
to

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.

Dmitriy Igrishin

unread,
Nov 4, 2012, 4:54:41 AM11/4/12
to
Hey Nikolaos,

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

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.


Jeff Davis

unread,
Nov 4, 2012, 12:04:00 PM11/4/12
to
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




--
Sent via pgsql-docs mailing list (pgsql...@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-docs

Nikolaos Oikonomopoulos

unread,
Nov 4, 2012, 12:32:59 PM11/4/12
to
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.
0 new messages