Re: implementing check-in/check-out of an items table

On 5/20/2011 10:15 AM, Seb wrote:
> On Fri, 20 May 2011 09:48:45 -0500,
> Jack Christensen<jackc@hylesanderson.edu>  wrote:
>> Use a loans table with unique partial index to ensure that only one
>> unreturned loan per item can exist at a time.
> [...]
>
> Thanks, this certainly avoids loaning an item before it's returned, but
> it doesn't protect against having loans that overlap in time.  For
> example, an item can have a start_time that is between start_time and
> end_time of a previous loan for that same item.  My first thought was to
> have some CHECK constraint with a query, but this doesn't seem to be
> supported by postgresql.
>
>
In a similar project I worked on start time for a loan was always the
current time so overlaps weren't an issue. I don't have any firsthand
experience with them, but it sounds like what you want are exclusion
constraints.

http://www.depesz.com/index.php/2010/01/03/waiting-for-8-5-exclusion-constraints/

--
Jack Christensen
jackc@hylesanderson.edu