pg.ddx.io pgsql-sql@postgresql.org mailing list archivehelp / color / mirror / Atom feed
diary constraints 6+ messages / 6 participants [nested] [flat]
* diary constraints @ 2005-08-23 08:33 Gary Stainburn <gary.stainburn@ringways.co.uk> 2005-08-23 08:58 ` Re: diary constraints Michael Glaesemann <grzm@myrealbox.com> 2005-08-23 09:00 ` Re: diary constraints Gnanavel S <s.gnanavel@gmail.com> 2005-08-23 09:06 ` Re: diary constraints PFC <lists@boutiquenumerique.com> 2005-08-23 09:12 ` Re: diary constraints A. Kretschmer <akretschmer@despammed.com> 0 siblings, 4 replies; 6+ messages in thread From: Gary Stainburn @ 2005-08-23 08:33 UTC (permalink / raw) To: pgsql-sql Hi folks I know this has been discussed in the past, but no amount of keywords has returned anything from the archives. 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. How do I go about creating constraints on inserts and updates to ensure that a) the finish is after the start b) two allocations for a single vehicle don't overlap. -- Gary Stainburn 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 ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: diary constraints 2005-08-23 08:33 diary constraints Gary Stainburn <gary.stainburn@ringways.co.uk> @ 2005-08-23 08:58 ` Michael Glaesemann <grzm@myrealbox.com> 3 siblings, 0 replies; 6+ messages in thread From: Michael Glaesemann @ 2005-08-23 08:58 UTC (permalink / raw) To: Gary Stainburn <gary.stainburn@ringways.co.uk>; +Cc: pgsql-sql On Aug 23, 2005, at 5:33 PM, Gary Stainburn wrote: > 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. > > How do I go about creating constraints on inserts and updates to > ensure > that > > a) the finish is after the start > b) two allocations for a single vehicle don't overlap. This is an interesting problem. You might want to take a look at this book, previously mentioned on the one of the lists (by George Essig, I believe): > Developing Time-Oriented Database Applications in SQL > by Richard T. Snodgrass > > The book is out of print, but the author has made the PDF available > on his website at: > http://www.cs.arizona.edu/people/rts/tdbbook.pdf Hope this helps! Michael Glaesemann grzm myrealbox com ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: diary constraints 2005-08-23 08:33 diary constraints Gary Stainburn <gary.stainburn@ringways.co.uk> @ 2005-08-23 09:00 ` Gnanavel S <s.gnanavel@gmail.com> 3 siblings, 0 replies; 6+ messages in thread From: Gnanavel S @ 2005-08-23 09:00 UTC (permalink / raw) To: Gary Stainburn <gary.stainburn@ringways.co.uk>; +Cc: pgsql-sql On 8/23/05, Gary Stainburn <gary.stainburn@ringways.co.uk> wrote: > > Hi folks > > I know this has been discussed in the past, but no amount of keywords > has returned anything from the archives. > > 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. > > How do I go about creating constraints on inserts and updates to ensure > that > > 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 > > 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 > > > ---------------------------(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 > -- with regards, S.Gnanavel Satyam Computer Services Ltd. ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: diary constraints 2005-08-23 08:33 diary constraints Gary Stainburn <gary.stainburn@ringways.co.uk> @ 2005-08-23 09:06 ` PFC <lists@boutiquenumerique.com> 3 siblings, 0 replies; 6+ messages in thread From: PFC @ 2005-08-23 09:06 UTC (permalink / raw) To: Gary Stainburn <gary.stainburn@ringways.co.uk>; pgsql-sql > a) the finish is after the start well, finish > start > b) two allocations for a single vehicle don't overlap. this one is a bit tricky ! - Check that there is no allocation in the table whose time period start, end includes either the start of the end of the reservation to insert, and that the time period of the reservation to insert does not contain either the start or end of any reservation in the table. This is 4 selects, playing with order by limit 1 and indexes, it will be fast. ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: diary constraints 2005-08-23 08:33 diary constraints Gary Stainburn <gary.stainburn@ringways.co.uk> @ 2005-08-23 09:12 ` A. Kretschmer <akretschmer@despammed.com> 3 siblings, 0 replies; 6+ messages in thread From: A. Kretschmer @ 2005-08-23 09:12 UTC (permalink / raw) To: pgsql-sql am 23.08.2005, um 9:33:58 +0100 mailte Gary Stainburn folgendes: > Hi folks > > I know this has been discussed in the past, but no amount of keywords > has returned anything from the archives. > > 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. > > How do I go about creating constraints on inserts and updates to ensure > that > > a) the finish is after the start with a check-constraint like this: create table foobar (t1 timestamp, t2 timestamp check (t2>t1)); > b) two allocations for a single vehicle don't overlap. possibly with a trigger. Regards, Andreas -- Andreas Kretschmer (Kontakt: siehe Header) Heynitz: 035242/47212, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net === Schollglas Unternehmensgruppe === ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: diary constraints @ 2005-08-23 08:57 neil.saunders@accenture.com 0 siblings, 0 replies; 6+ messages in thread From: neil.saunders@accenture.com @ 2005-08-23 08:57 UTC (permalink / raw) To: pgsql-sql Hi Gary, I've actually just done the same thing - but for renting property. I've implemented the constraint as a trigger (Before insert/update, for each row), that first checks if the start_date is < end_date, and then performs a select on the bookings table using the OVERLAPS function. If there are more than 0 records returned, an exception is raised. I've included the code below. You shouldn't need too many changes to adapt it to your needs! /* This trigger function is responsible for ensuring temporal integrity within the calendar_entries table (And it's children). It ensures that only entries with no overlapping entries. */ BEGIN /* First, check that the start_date > end_date */ IF NEW.start_date > NEW.end_date THEN RAISE EXCEPTION 'ERROR: start_date must not be greater than end_date'; END IF; IF EXISTS ( SELECT 1 FROM calendar_entries WHERE ((start_date,end_date) OVERLAPS (NEW.start_date, NEW.end_date)) AND property_id = NEW.property_id LIMIT 1 ) THEN RAISE EXCEPTION 'cannot add booking - overlapping calendar entries detected'; END IF; RETURN NEW; END; This message is for the designated recipient only and may contain privileged, proprietary, or otherwise private information. If you have received it in error, please notify the sender immediately and delete the original. Any other use of the email by you is prohibited. ^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2005-08-23 09:12 UTC | newest] Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2005-08-23 08:33 diary constraints Gary Stainburn <gary.stainburn@ringways.co.uk> 2005-08-23 08:58 ` Michael Glaesemann <grzm@myrealbox.com> 2005-08-23 09:00 ` Gnanavel S <s.gnanavel@gmail.com> 2005-08-23 09:06 ` PFC <lists@boutiquenumerique.com> 2005-08-23 09:12 ` A. Kretschmer <akretschmer@despammed.com> 2005-08-23 08:57 Re: diary constraints neil.saunders@accenture.com
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox