public inbox for [email protected]  

help / color / mirror / Atom feed

CONSTRAINT on ARRAY ELEMENTS
4+ messages / 4 participants
[nested] [flat]

* CONSTRAINT on ARRAY ELEMENTS
@ 2012-11-03 09:43 Nikolaos Ikonomopoulos <[email protected]>
  2012-11-04 09:54 ` Re: CONSTRAINT on ARRAY ELEMENTS Dmitriy Igrishin <[email protected]>
  2012-11-04 17:04 ` Re: CONSTRAINT on ARRAY ELEMENTS Jeff Davis <[email protected]>
  0 siblings, 2 replies; 4+ messages in thread

From: Nikolaos Ikonomopoulos @ 2012-11-03 09:43 UTC (permalink / raw)
  To: 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.
 		 	   		  

^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: CONSTRAINT on ARRAY ELEMENTS
  2012-11-03 09:43 CONSTRAINT on ARRAY ELEMENTS Nikolaos Ikonomopoulos <[email protected]>
@ 2012-11-04 09:54 ` Dmitriy Igrishin <[email protected]>
  1 sibling, 0 replies; 4+ messages in thread

From: Dmitriy Igrishin @ 2012-11-04 09:54 UTC (permalink / raw)
  To: Nikolaos Ikonomopoulos <[email protected]>; +Cc: pgsql-docs

Hey Nikolaos,

2012/11/3 Nikolaos Ikonomopoulos <[email protected]>

>
> 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.


^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: CONSTRAINT on ARRAY ELEMENTS
  2012-11-03 09:43 CONSTRAINT on ARRAY ELEMENTS Nikolaos Ikonomopoulos <[email protected]>
@ 2012-11-04 17:04 ` Jeff Davis <[email protected]>
  2012-11-04 17:32   ` Re: CONSTRAINT on ARRAY ELEMENTS Nikolaos Oikonomopoulos <[email protected]>
  1 sibling, 1 reply; 4+ messages in thread

From: Jeff Davis @ 2012-11-04 17:04 UTC (permalink / raw)
  To: Nikolaos Ikonomopoulos <[email protected]>; +Cc: 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






^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: CONSTRAINT on ARRAY ELEMENTS
  2012-11-03 09:43 CONSTRAINT on ARRAY ELEMENTS Nikolaos Ikonomopoulos <[email protected]>
  2012-11-04 17:04 ` Re: CONSTRAINT on ARRAY ELEMENTS Jeff Davis <[email protected]>
@ 2012-11-04 17:32   ` Nikolaos Oikonomopoulos <[email protected]>
  0 siblings, 0 replies; 4+ messages in thread

From: Nikolaos Oikonomopoulos @ 2012-11-04 17:32 UTC (permalink / raw)
  To: 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.





^ permalink  raw  reply  [nested|flat] 4+ messages in thread

end of thread, other threads:[~2012-11-04 17:32 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-11-03 09:43 CONSTRAINT on ARRAY ELEMENTS Nikolaos Ikonomopoulos <[email protected]>
2012-11-04 09:54 ` Dmitriy Igrishin <[email protected]>
2012-11-04 17:04 ` Jeff Davis <[email protected]>
2012-11-04 17:32   ` Nikolaos Oikonomopoulos <[email protected]>

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox