Skip site navigation (1) Skip section navigation (2)

Re: Finding overlapping records

From: Jon Erdman <postgresql(at)thewickedtribe(dot)net>
To: Frank Sheiness <frank(at)korcett(dot)com>
Cc: austinpug(at)postgresql(dot)org
Subject: Re: Finding overlapping records
Date: 2009-12-10 20:52:08
Message-ID: 4B215F78.9030505@thewickedtribe.net (view raw or flat)
Thread:
Lists: austinpug
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


Hmm...reading my message now, I realize this bit might have been
unclear. To find things that *don't* overlap is straightforward, you can
say:

WHERE (e2 < s1) OR (s2 > e1);

so the trick is, you can simply invert that to find things that *do*
overlap:

WHERE NOT ((e2 < s1) OR (s2 > e1));

and it uses indexes and catches all cases! ;)

Frank Sheiness wrote:
> I think you are right about the other cases.  Especially about the case
> where the newer lease resides entirely inside the older lease.  I noticed
> that one right after I sent the email last night and updated my trigger for
> it.
> 
> As for the other ones, I was just depending on the order in the lease file
> to protect me.
> 
> We're still on 8.2 for now.  I started to look at the period data type from
> John Davis and will play with it today.
> 
> On Thu, Dec 10, 2009 at 11:15:38AM -0600, Jon Erdman wrote:
> 
> Doh. Just read your whole message, so I see why you were only checking
> for the one case. I think the full discussion is still worthwhile though.
> 
> Jon Erdman wrote:
>>>> Frank,
>>>>
>>>> First of all, you've got a big hole in your overlap check. You're only
>>>> checking for (new span is --- existing is +++):
>>>>
>>>>     *+++++++*
>>>>        *-------*
>>>>
>>>> when you really need to check for:
>>>>
>>>>    *++++++++*
>>>>      *---------*
>>>> *-------*
>>>> *--------------*
>>>>      *---*
>>>>
>>>> /me pulls out Celko's SQL For Smarties...
>>>>
>>>> So what you would naturally write is perhaps (s1 and e1 are start and
>>>> end of existing span, s2 and e2 are the new span):
>>>>
>>>> WHERE
>>>>    s2 between s1 and e1
>>>> OR e2 between s1 and e1
>>>> OR s1 between s2 and e2
>>>> OR e1 between s2 and e2;
>>>>
>>>> which is a bit long and ugly. There's a shortcut you can take, here's
>>>> how you would search for things that *don't* overlap:
>>>>
>>>>          *+++++*
>>>> *----*
>>>>                   *-----*
>>>>
>>>> so you can write it as:
>>>>
>>>> WHERE NOT ((e2 < s1) OR (s2 > e1));
>>>>
>>>> which is *much* cleaner, no? ;)
>>>>
>>>> Credit goes to Joe Celko, SQL for Smarties, Chapter 13: Between and
>>>> Overlaps Predicate, 13.2 Overlaps Predicate, page 279.
>>>>
>>>> Postgres actually has OVERLAPS, so you can just say:
>>>>
>>>> WHERE (s2, e2) OVERLAPS (s1, e1);
>>>>
>>>> however, at least in 8.1, that doesn't use the indexes on the start_date
>>>> and end_date. The shortcut above does use those indexes and is nice and
>>>> fast.
>>>>
>>>> You should test and see if 8.3 or 8.4 will use the indexes for OVERLAPS
>>>> though...

- --

Jon T Erdman

Chief Information Officer            voice:       (210) 400-5717
Progressive Practice, Inc.           jon(at)progressivepractice(dot)com
P.O. Box 17288                       www.progressivepractice.com
Rochester, NY 14617


-----BEGIN PGP SIGNATURE-----

iEYEARECAAYFAkshX3gACgkQRAk1+p0GhSHy4QCdH7jvcQUVlaATLdD2GXeqSEsC
gsAAn1KkrHcfNuyBrQqWONWEFNYM3c12
=Ux3W
-----END PGP SIGNATURE-----

In response to

Responses

austinpug by date

Next:From: Greg SmithDate: 2009-12-15 17:27:43
Subject: Closing out CommitFest 2009-11
Previous:From: Frank SheinessDate: 2009-12-10 20:42:56
Subject: Re: Finding overlapping records

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group