Received: from malur.postgresql.org ([217.196.149.56])
	by arkaria.postgresql.org with esmtps  (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384
	(Exim 4.94.2)
	(envelope-from <pgsql-general-owner+archive@lists.postgresql.org>)
	id 1tZYQ1-00AlzF-BA
	for pgsql-general@arkaria.postgresql.org; Sun, 19 Jan 2025 16:45:01 +0000
Received: from localhost ([127.0.0.1] helo=malur.postgresql.org)
	by malur.postgresql.org with esmtp (Exim 4.94.2)
	(envelope-from <pgsql-general-owner+archive@lists.postgresql.org>)
	id 1tZYPy-008TzM-11
	for pgsql-general@arkaria.postgresql.org; Sun, 19 Jan 2025 16:44:58 +0000
Received: from makus.postgresql.org ([2001:4800:3e1:1::229])
	by malur.postgresql.org with esmtps  (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384
	(Exim 4.94.2)
	(envelope-from <tgl@sss.pgh.pa.us>)
	id 1tZYPx-008TzD-Kx
	for pgsql-general@lists.postgresql.org; Sun, 19 Jan 2025 16:44:58 +0000
Received: from sss.pgh.pa.us ([68.162.161.243])
	by makus.postgresql.org with esmtps  (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384
	(Exim 4.96)
	(envelope-from <tgl@sss.pgh.pa.us>)
	id 1tZYPv-000Pi0-1v
	for pgsql-general@lists.postgresql.org;
	Sun, 19 Jan 2025 16:44:56 +0000
Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1])
	by sss.pgh.pa.us (8.15.2/8.15.2) with ESMTP id 50JGirXu2739255;
	Sun, 19 Jan 2025 11:44:53 -0500
From: Tom Lane <tgl@sss.pgh.pa.us>
To: "David G. Johnston" <david.g.johnston@gmail.com>
cc: Brent Wood <Brent.Wood@niwa.co.nz>,
        "pgsql-general@lists.postgresql.org" <pgsql-general@lists.postgresql.org>
Subject: Re: concatenating hstores in a group by?
In-reply-to: <CAKFQuwasxOzkA-BECEaOz1b8EOh2hGaEfcG3JHquL-eDJkSrMQ@mail.gmail.com>
References: <CH2PR01MB5765698BB9F71CEB662D5AE4AD1B2@CH2PR01MB5765.prod.exchangelabs.com> <2268303.1737134384@sss.pgh.pa.us> <SY7P300MB0761A579730344B2813CA07DA11B2@SY7P300MB0761.AUSP300.PROD.OUTLOOK.COM> <CAKFQuwasxOzkA-BECEaOz1b8EOh2hGaEfcG3JHquL-eDJkSrMQ@mail.gmail.com>
Comments: In-reply-to "David G. Johnston" <david.g.johnston@gmail.com>
	message dated "Sun, 19 Jan 2025 08:28:32 -0700"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <2739253.1737305093.1@sss.pgh.pa.us>
Date: Sun, 19 Jan 2025 11:44:53 -0500
Message-ID: <2739254.1737305093@sss.pgh.pa.us>
List-Id: <pgsql-general.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-general@lists.postgresql.org>
List-Owner: <mailto:pgsql-general-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-general>
Archived-At: <https://www.postgresql.org/message-id/2739254.1737305093%40sss.pgh.pa.us>
Precedence: bulk

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Friday, January 17, 2025, Brent Wood <Brent.Wood@niwa.co.nz> wrote:
>> I want to concatenate the hstores,

> There are no hstore aggregate functions.

It would be trivial to make a user-defined one.
More or less (untested)

create aggregate hstore_agg(hstore)
(
  sfunc = hs_concat,
  stype = hstore
);

Either way though, the order of aggregation is unspecified and thus
it's unclear which input will win when there are duplicate keys.
If that matters then you'll need to think about how you want
to resolve it.

			regards, tom lane