agora inbox for pgsql-sql@postgresql.org  

help / color / mirror / Atom feed

From: dave <audiotecture@web.de>
To: pgsql-sql@postgresql.org
Subject: Recursive merging of overlapping arrays in a column
Date: Sun, 20 Sep 2015 04:12:36 -0700 (MST)
Message-ID: <1442747556700-5866560.post@n5.nabble.com> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>

Hey mailing list,

i have the following Table:



I want to merge the arrays which have overlapping elements, so that I get
the result which doesn't contain overlapping arrays anymore:


I am not an expert in SQL and it took me a long time to come up with this
solution:


Which gives me the result:


Result number 3 is contained in number one and shouldn't be in the output
anymore, because I only want non overlapping arrays in the result.

Another problem I encountered is that the performance of this query seems to
be very bad. I tried running it on a larger table (~400000 arrays) and it is
still running after ~10h.

I would appreciate any input on this problems, so it would be nice if anyone
could give me a hint how to get only the merged arrays without overlaps in
the resultset and maybe how to build a more elegant and efficient query.


Thanks in advance,

Dave



--
View this message in context: http://postgresql.nabble.com/Recursive-merging-of-overlapping-arrays-in-a-column-tp5866560.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

view thread (7+ messages)  latest in thread

Message-ID: <1442747556700-5866560.post@n5.nabble.com>
Permalink:  ../1442747556700-5866560.post@n5.nabble.com/
Also on:    postgresql.org/message-id/1442747556700-5866560.post@n5.nabble.com

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: audiotecture@web.de
  Subject: Re: Recursive merging of overlapping arrays in a column
  In-Reply-To: <1442747556700-5866560.post@n5.nabble.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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