Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1Zdxfq-0007QQ-Co for pgsql-sql@arkaria.postgresql.org; Mon, 21 Sep 2015 09:46:14 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84) (envelope-from ) id 1Zdxfp-0005lq-I4 for pgsql-sql@arkaria.postgresql.org; Mon, 21 Sep 2015 09:46:13 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84) (envelope-from ) id 1Zdxfm-0005kI-Gs for pgsql-sql@postgresql.org; Mon, 21 Sep 2015 09:46:10 +0000 Received: from plane.gmane.org ([80.91.229.3]) by makus.postgresql.org with esmtps (TLS1.0:RSA_AES_256_CBC_SHA1:256) (Exim 4.84) (envelope-from ) id 1Zdxff-00062W-Nm for pgsql-sql@postgresql.org; Mon, 21 Sep 2015 09:46:09 +0000 Received: from list by plane.gmane.org with local (Exim 4.69) (envelope-from ) id 1ZdxfU-000137-Dd for pgsql-sql@postgresql.org; Mon, 21 Sep 2015 11:45:52 +0200 Received: from static.230.116.9.176.clients.your-server.de ([176.9.116.230]) by main.gmane.org with esmtp (Gmexim 0.1 (Debian)) id 1AlnuQ-0007hv-00 for ; Mon, 21 Sep 2015 11:45:52 +0200 Received: from hari.fuchs by static.230.116.9.176.clients.your-server.de with local (Gmexim 0.1 (Debian)) id 1AlnuQ-0007hv-00 for ; Mon, 21 Sep 2015 11:45:52 +0200 X-Injected-Via-Gmane: http://gmane.org/ To: pgsql-sql@postgresql.org From: hari.fuchs@gmail.com Subject: Re: Recursive merging of overlapping arrays in a column Date: Mon, 21 Sep 2015 11:45:43 +0200 Lines: 30 Message-ID: <87wpvkxfzs.fsf@hf.protecting.net> References: <1442747556700-5866560.post@n5.nabble.com> <55FEBBE0.8090902@gmail.com> <1442764637710-5866579.post@n5.nabble.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Complaints-To: usenet@ger.gmane.org X-Gmane-NNTP-Posting-Host: static.230.116.9.176.clients.your-server.de X-Archive: encrypt X-PGP-Fingerprint: 09 4E C4 A2 B2 C5 33 1A 79 80 5D 39 BD 9B 89 39 X-Face: (1awP+uzUZkz*UdAvr%F%K`x9g3n,CWkrK[r6TS,kY~DP)$C&=IJQ; H0uPn0B$Rb>\w"Tk~9w'; 1@dNIad{z!y(R99X7d-uc~Vf%,i.:=~=V>.b_)hr36Jt.tF0OBe]&PB6F.(k[i^^v^8DBny^)@17gud{[!1jfZ8+ User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/23.4 (gnu/linux) Cancel-Lock: sha1:BsNnh/4VA1INe/k9qx1HALkPVbg= X-Pg-Spam-Score: -1.7 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-sql Precedence: bulk Sender: pgsql-sql-owner@postgresql.org dave writes: > Sorry, here is the post again in plain text... > > i have the following Table: > > CREATE TABLE arrays (id SERIAL, arr INT[]); > INSERT INTO arrays (arr) VALUES (ARRAY[1,3,6,9]); > INSERT INTO arrays (arr) VALUES (ARRAY[2,4]); > INSERT INTO arrays (arr) VALUES (ARRAY[3,10,40]); > INSERT INTO arrays (arr) VALUES (ARRAY[3,18,44]); > INSERT INTO arrays (arr) VALUES (ARRAY[63,140,420]); > INSERT INTO arrays (arr) VALUES (ARRAY[42,102,420]); > INSERT INTO arrays (arr) VALUES (ARRAY[2,7]); > INSERT INTO arrays (arr) VALUES (ARRAY[1,3,11]); > INSERT INTO arrays (arr) VALUES (ARRAY[8,12,19]); > > > I want to merge the arrays which have overlapping elements, so that I get > the result which doesn't contain overlapping arrays anymore: > > arr > -------------------------- > {1,3,6,9,10,11,18,40,44} > {2,4,7} > {8,12,19} > {42,63,102,140,420} The "intarray" extension (see Appendix F of the fine manual) provides an "overlaps" operator "&&". -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql