X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 8C772D705E
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 23 Aug 2005 06:00:12 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 49902-10
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 23 Aug 2005 09:00:09 +0000 (GMT)
Received: from zproxy.gmail.com (zproxy.gmail.com [64.233.162.198])
	by svr1.postgresql.org (Postfix) with ESMTP id 3AECFD7063
	for <pgsql-sql@postgresql.org>; Tue, 23 Aug 2005 06:00:08 -0300 (ADT)
Received: by zproxy.gmail.com with SMTP id z6so807132nzd
	for <pgsql-sql@postgresql.org>; Tue, 23 Aug 2005 02:00:10 -0700 (PDT)
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references;
	b=sgu36+2TDtD1FkCT7Z1Tpgo0CZxBfcq0CTfhCdMHe1RaCWnDGWRjb2jq16+NK17yDUNYw90VnaFU0EN9eHpF8UL03eluIr2znYgWMSX3HxMxoOP5eYPCPyqfGfNBp9+QGP4mr2JXjGZClxsiOE4FCrRkCIDCa17e0uDrfwB6xgA=
Received: by 10.36.47.16 with SMTP id u16mr5166048nzu;
	Tue, 23 Aug 2005 02:00:10 -0700 (PDT)
Received: by 10.36.119.19 with HTTP; Tue, 23 Aug 2005 02:00:10 -0700 (PDT)
Message-ID: <eec3b03c0508230200127dd262@mail.gmail.com>
Date: Tue, 23 Aug 2005 14:30:10 +0530
From: Gnanavel S <s.gnanavel@gmail.com>
To: Gary Stainburn <gary.stainburn@ringways.co.uk>
Subject: Re: diary constraints
Cc: pgsql-sql@postgresql.org
In-Reply-To: <200508230933.59043.gary.stainburn@ringways.co.uk>
Mime-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_2622_31472240.1124787610712"
References: <200508230933.59043.gary.stainburn@ringways.co.uk>
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.316 required=5 tests=[AWL=0.205, HTML_40_50=0.086,
	HTML_MESSAGE=0.001, RCVD_BY_IP=0.024]
X-Spam-Level: 
X-Archive-Number: 200508/200
X-Sequence-Number: 22428

------=_Part_2622_31472240.1124787610712
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

On 8/23/05, Gary Stainburn <gary.stainburn@ringways.co.uk> wrote:
>=20
> Hi folks
>=20
> I know this has been discussed in the past, but no amount of keywords
> has returned anything from the archives.
>=20
> I want to create a courtesy car diary diary system where I have a table
> containing all of the cortesy cars in the pool, and then an allocation
> table which has two timestamps, one for the start date/time and one for
> the return date/time.
>=20
> How do I go about creating constraints on inserts and updates to ensure
> that
>=20
> a) the finish is after the start


check constraint like -> "finish_time > start_time" this will do

b) two allocations for a single vehicle don't overlap.


Use "overlaps" function in a trigger to validate the above.

--
> Gary Stainburn
>=20
> This email does not contain private or confidential material as it
> may be snooped on by interested government parties for unknown
> and undisclosed purposes - Regulation of Investigatory Powers Act, 2000
>=20
>=20
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>=20



--=20
with regards,
S.Gnanavel
Satyam Computer Services Ltd.

------=_Part_2622_31472240.1124787610712
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

<br><br><div><span class=3D"gmail_quote">On 8/23/05, <b class=3D"gmail_send=
ername">Gary Stainburn</b> &lt;<a href=3D"mailto:gary.stainburn@ringways.co=
.uk">gary.stainburn@ringways.co.uk</a>&gt; wrote:</span><blockquote class=
=3D"gmail_quote" style=3D"border-left: 1px solid rgb(204, 204, 204); margin=
: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
Hi folks<br><br>I know this has been discussed in the past, but no amount o=
f keywords<br>has returned anything from the archives.<br><br>I want to cre=
ate a courtesy car diary diary system where I have a table<br>containing al=
l of the cortesy cars in the pool, and then an allocation
<br>table which has two timestamps, one for the start date/time and one for=
<br>the return date/time.<br><br>How do I go about creating constraints on =
inserts and updates to ensure<br>that<br><br>a) the finish is after the sta=
rt
</blockquote><div><br>
check constraint like -&gt; &quot;finish_time &gt; start_time&quot; this wi=
ll do<br>
</div><br><blockquote class=3D"gmail_quote" style=3D"border-left: 1px solid=
 rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">b) two =
allocations for a single vehicle don't overlap.</blockquote><div><br>
Use &quot;overlaps&quot; function in a trigger to validate the above.<br>
</div><br><blockquote class=3D"gmail_quote" style=3D"border-left: 1px solid=
 rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">--<br>G=
ary Stainburn<br><br>This email does not contain private or confidential ma=
terial as it
<br>may be snooped on by interested government parties for unknown<br>and u=
ndisclosed purposes - Regulation of Investigatory Powers Act, 2000<br><br><=
br>---------------------------(end of broadcast)---------------------------
<br>TIP 1: if posting/reading through Usenet, please send an appropriate<br=
>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; subscribe-nomail command to <a href=
=3D"mailto:majordomo@postgresql.org">majordomo@postgresql.org</a> so that y=
our<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; message can get through to the =
mailing list cleanly
<br></blockquote></div><br><br clear=3D"all"><br>-- <br>with regards,<br>S.=
Gnanavel<br>Satyam Computer Services Ltd.

------=_Part_2622_31472240.1124787610712--