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 ) id 1taIZo-000ZGo-Gk for pgsql-general@arkaria.postgresql.org; Tue, 21 Jan 2025 18:02:13 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1taIZn-002zx0-ER for pgsql-general@arkaria.postgresql.org; Tue, 21 Jan 2025 18:02:11 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1taIZm-002zwr-Q6 for pgsql-general@lists.postgresql.org; Tue, 21 Jan 2025 18:02:11 +0000 Received: from sonic303-22.consmr.mail.ne1.yahoo.com ([66.163.188.148]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1taIZj-000l8c-1J for pgsql-general@postgresql.org; Tue, 21 Jan 2025 18:02:10 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1737482524; bh=GBt2C82BYQWQ3E6bUZ0C+za1ST+vptBUdwTb+Ge7ceA=; h=Date:From:To:In-Reply-To:References:Subject:From:Subject:Reply-To; b=YfzuLNTxa2tQKkPOa9QfbEfjS0KAASUdGlEeA9aO00ww3n2WyrlfwPGj58az5etCU1i4iv8WTJ9d/RxKl1QY1zvJJEjJLSbEgA+8jJbkodi4oUKGcNzPDmJcBS3V3XNi2fvrcFlVqz+Vt9WO5+wkQUXiLYwzj99VHilNi2om1Gs87iNpAtF7A9Lgfv8ezIRQsgVvtYZsOPBNl1R8OvGnVi6X+AWMWjbc8LbR/UZQM0ZSstaaVW94cjmETeyLl3fSuCLlsR0sdG0mC/+MzTUKj0G5T9Ch94/NvB2HH3yWMeVS9q5PiladY9W9Bxz2u4kUMTPvTbEVkOO3N35WjWpdnQ== X-SONIC-DKIM-SIGN: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1737482524; bh=jLErrWan8RH+kBJP08nI9TcYPBBeDr07wtnqyWYlqaa=; h=X-Sonic-MF:Date:From:To:Subject:From:Subject; b=OgpYzAzZct8KUQ6qBjIK+St4kmrHAVNEbqXPw9aGmNO3KUNRM8P7q09PkZDlZzciPHPRweT9r6VYbl3ZNgcfxUwln37/EypQIo/za6owTTi/nRtqMhH5vzPy7vJty3/bwWb228pG13+EDw4qXzhrDrbmMtcfa5lEii9fI8LSgg0Xr6uq9rx5QA0b2rP7R7Lhh5Sie10kZsSWw3lJmHzGDAqOETyvn81ihaAKPpbG+vgKTk3flkKotslm9A9yc5sen7f8WWOj/1Xug4J0lY+V7falhGTDHylrU7ujD933gr1pc6NorxqbpnHXKY9DheRv4MGLzsjzurgZCOroteU9nQ== X-YMail-OSG: d1EPb7QVM1l1_Rxjg8C1eYGiuoM18_N8a_v3xsq_QeAaFOQhmCXJUnMyZi.bEsK rDHG36pcARXv_NWFwN0ZtImsPsDdklCv0UpNX4FptX2ekN0_.nzaduTIKHAlBEZ_W2033OHb8bU8 _5WyGAjZCNi3JWFSqIsQkaLPZ9nisTNTi1u2bd_oIJexzObtwGyOxSsamtE8A1htz75WbQHg.z_S oMIV0yHtXUI2u033LgWzQn4yZg1DVe47W9.7_0iOLdUHqHRq04TyasjrAnckoC8kRR_xXdMdgyFL cUN6OVqkaAVpf01Rb8er08eb3djZKBiGM5cLxAcIfTaCGWSFXweGF2rewYzhOtuwf3yqYsM0QnHu uPVQ55TYbyKql39z9D90E62S8K_0tB47AK5PG1WtPMvupZi_KNCuI4Rb1aiKaesN9FJgdTgWj1HE pvx0Zpgh7YTSo6a37.Ho5gyArTlqIXhShu0PCMiZBc_ORp5Tu0lIHPzqA6JL8Fjd1Rxz8A5TDf9J F11eUfyTsEBBpRd6XLcFQsIa4u7zMzgX3.2BUtVEnI6.kMr4p5TBIm9oDdLzD1ISrnizgKQdt6_K ErfW4vuFZp6ZFQtckiv4Y.FRGiBKOslEBpFxzylIGRmC1We5ibi1GYECIQFqpsU2NB_LwrjAXjT1 hCoFVOc8Dd2O7wp0BOoQhZYthOk9olJjFGWVn6n1UvNtrEXwOHOnMSGL3TtiEvWpHkTSpbgmO_lj hpjdtJ6fb38UaXb4iYHx3tbXr8lFz2yXhcIbxvHS0ZAOnBUGr0jgd2VxUxllkPcsrPuDzj7mULJN WpD.CBuKidSthNCt3vSq4NaooIH02LyqVt3KbeQvEmlmiVm2X0rTmZd6dD.W1yY2Tt99iWGhUxLA 4BtzZdP5vW3C0Mz1fjQd1OTYJhV4407lh0KT3KxsVu0pXR1DVm2OjSi1J1_luhiDBHHnTRp0DL5s c.q3qlf0l37VfjzPA_UFhngQ_LnE_eY.TJdWgovSGNGpvTuOLaa0ArCQAyYzLV2SAPQOJ0dHzBOv T96354P3uAefXf.5Gu.aml6Pfz4qmayyJKm4tI06EZSPGoiY.Dj4pZhu3kfnl2AZpd6DReFprs2s pcZ_Yw5oMwCybLNem1tGDnbCxuRr5jiWurovg7qPgYn_X5eLiYtbdBVP8BOiA5b99MhlqbOGoz2a lCEoIeeO_ouqXjbNvqWe_cA9RYXh9owdwMTOP_TLvST8NaS.NjBD_uPtvh0Dbi.dFx_1Ze1KPa9Y aXNd04Wcx0JvcWov3xGajE0jX5OtLn8DAwHPK9nRMAfNl_bOjdhtHn1cdmK4LFLW5PlHsCFr85fK Ik237wXMkNujPrmiB8nugbMmXfjU3SiCc1YbDQRA924G0dNkXNdLtcWtsnIvvGZTm5c0bY8CUtAD s50zkO0An6y8lkWsHLlQGD2dZmHrHPmrOzcnxqqkF.eMYIm2dkHLHbLeiYp7mHbZV785OW7mMTGp KV9dps0zudrjX5xiyDpNF.B4Zx8.1T0tWYU.W9H85UyoROy066GXy6KtyN5UD1lBqEJUb7FPJoZO P8HflojawuTRE6EP2B2rgNxLtzhJp6WBMFcW00lzeN0i4DpIrVV9860PrdI0nsUP2ci2XWEhOEsB E1vpVzPU_0vtTdJzzjwwaAsmyteW8CL1CEiZF.4dEIWheGAgXa42L0mMi__d9jVQz5FWTxLZmh9R jix.97h_VQIwjKoD1k.cocSmf233RafXJW7o31kU_ASaztjLN5vBsoCr6F7pWZytnGoDogxAWF_y 6BAEdbgBJnzbm2jj.05TirSk_.LbPrI3kjqQIPUg_CGn4HL7c0pWOBMgOXe2eBHfnc4bYe9z7EFJ MThdCNmFRKlvR06L6Dvlwnk2os.tbEHedQabm_xyJ_hT1rRIdAwMjWicWnlkKE5fFkjaFpjKKLD_ lryXRL4OlvtTCkX8Tv3uC45oiympMPH768iYqYAExf7IoYPXfVPgn5az3FLsfSq5C2n7K8nxRz1d Pkv9fyDNlPOmIvZDsKD.uq_zYhe2kOdVQ0WbDNjD3VXYYehCB1BNmxJq3Kt87rLfVApT83Dz8PUc Kl83lYjla0PRf3mDBb8D_335ZzqLil0AP2YUPdgeBC8W7chgXkaoERqQGwDbnEBpP7V41K2uWdEu qqTjr66GpjzI3jQoGaGLRB7ANoord153FC6UaHLSiJzVDVSLP2.tWNeXVwhqqunOTKSLw5kOOEWZ Aa7POoFzHcBG0oqmadynUXGPKnkXwysvtcWmH_d4tT8iQHw-- X-Sonic-MF: X-Sonic-ID: 1765b2ec-ad4a-4680-88e0-980819025e47 Received: from sonic.gate.mail.ne1.yahoo.com by sonic303.consmr.mail.ne1.yahoo.com with HTTP; Tue, 21 Jan 2025 18:02:04 +0000 Date: Tue, 21 Jan 2025 18:02:01 +0000 (UTC) From: Brent Wood To: Adrian Klaver , Pgsql-general General Message-ID: <660172279.2625291.1737482521074@mail.yahoo.com> In-Reply-To: <9d1dfa7a-4d1d-40c2-960e-5d9240217245@aklaver.com> References: <2268303.1737134384@sss.pgh.pa.us> <9d1dfa7a-4d1d-40c2-960e-5d9240217245@aklaver.com> Subject: Re: concatenating hstores in a group by? MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_2625290_1461253203.1737482521072" X-Mailer: WebService/1.1.23187 YMailNorrin Content-Length: 11707 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk ------=_Part_2625290_1461253203.1737482521072 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Yes, a Timescale hypertable with 500,000,000 rows of about 15 key/value pa= irs per record. I'm not sure why there is both a gin & gist index on the hstore, or the mer= its of each. Thanks.... =C2=A0\d t_reading_hstore_sec =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0 Table "public.t_reading_hstore_sec" =C2=A0=C2=A0 Column=C2=A0=C2=A0 |=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0 Type=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0 | Collation | Nullable |=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 Default=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0 =C2=A0 ------------+-----------------------------+-----------+----------+---------= ------------------------------------------ =C2=A0key=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 | bigint=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 |=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0 | not null | nextval('t_reading_hstore_sec_key_seq= '::regclass) =C2=A0timer=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 | timestamp without time zone |= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 | not null |= =20 =C2=A0values_sec | hstore=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0 |=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 |=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 |=20 Indexes: =C2=A0=C2=A0=C2=A0 "t_reading_hstore_sec_pkey" PRIMARY KEY, btree (key, tim= er) =C2=A0=C2=A0=C2=A0 "t_reading_hstore_sec_timer_idx" btree (timer) =C2=A0=C2=A0=C2=A0 "t_reading_hstore_sec_timer_key" UNIQUE CONSTRAINT, btre= e (timer) =C2=A0=C2=A0=C2=A0 "t_reading_hstore_sec_values_idx_gin" gin (values_sec) =C2=A0=C2=A0=C2=A0 "t_reading_hstore_sec_values_idx_gist" gist (values_sec) On Wednesday, January 22, 2025 at 06:34:38 AM GMT+13, Adrian Klaver wrote: =20 =20 On 1/19/25 12:09, Brent Wood wrote: > Thanks for the replies, appreciated... >=20 > My current solution is: >=20 > /select trip_code,/ > /=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 station_no,/ > /=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 timer_sec + interval '12 hour'= as NZST,/ > /=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 timer_sec as utc,/ > /=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 hstore_to_json(string_agg(valu= es_sec::text, ', ')::hstore)=20 > as values_sec/ > /=C2=A0 =C2=A0 =C2=A0from (select '$TRIP' as trip_code,/ > /=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 $STATION = as station_no,/ > /=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 date_trun= c('second', timer) as timer_sec,/ > /=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 values_se= c/ > /=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0from t_reading_hstore_sec/ > /=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0where timer >=3D '$ISO_S'::time= stamp - interval '12 hour'/ > /=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0and timer <=3D '$ISO_F':= :timestamp - interval '12 hour') as foo/ > /group by timer_sec, trip_code, station_no;/ >=20 > Convert the hstore to text, aggregate the text with string_agg(),=20 > convert back to hstore (which seems to remove duplicate keys, OK for my= =20 > purpose) To be clear values_sec in t_reading_hstore_sec is the hstore field? If so what is it's structure? > and group by timer truncated to whole seconds. I also provide UTC &=20 > local timezone times for each set of readings. It is run in a bash=20 > script which passes the trip & station values to the query, as well as=20 > the start/finish times as ISO format strings. >=20 > The output is going to a Sqlite3 (Spatialite) database, which does not=20 > have hstore, or all the hstore functionality that Postgres has, but does= =20 > have a json datatype which is adequate for our purposes, hence the=20 > hstore_to_json in the query. >=20 >=20 > Thanks again, >=20 > Brent Wood >=20 --=20 Adrian Klaver adrian.klaver@aklaver.com =20 ------=_Part_2625290_1461253203.1737482521072 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Yes, a Timescale hypertable = with 500,000,000 rows of about 15 key/value pairs per record.

I'm not sure why there is both a gin & gist index on the hstore, o= r the merits of each.


Thanks....
 \d t_reading_hstore_sec            &= nbsp;           &nbs= p;            &= nbsp;   Table "public.t_reading_hstore_sec"
   Colum= n   |          =   Type          &nbs= p;  | Collation | Nullable |       =             &nb= sp;  Default         &nbs= p;            
--= ----------+-----------------------------+-----------+----------+-----------= ----------------------------------------
 key   &nbs= p;    | bigint       &nbs= p;            &= nbsp; |           | not n= ull | nextval('t_reading_hstore_sec_key_seq'::regclass)
 timer = ;     | timestamp without time zone |   =         | not null |
 values_se= c | hstore           = ;           |  =          |    &= nbsp;     |
Indexes:
    "t_readi= ng_hstore_sec_pkey" PRIMARY KEY, btree (key, timer)
    "= t_reading_hstore_sec_timer_idx" btree (timer)
    "t_read= ing_hstore_sec_timer_key" UNIQUE CONSTRAINT, btree (timer)
  &= nbsp; "t_reading_hstore_sec_values_idx_gin" gin (values_sec)
  = ;  "t_reading_hstore_sec_values_idx_gist" gist (values_sec)
=



=20
=20
On Wednesday, January 22, 2025 at 06:34:38 AM GMT+1= 3, Adrian Klaver <adrian.klaver@aklaver.com> wrote:


=20 =20
On 1/19/25 12:09, Brent Wood wrote:> Thanks for the replies, appreciated...
>
> My current solution is:
= >
> /select trip_code,/
> /&n= bsp;           station_no,/
>= /            timer_sec + interval '12 hour' = as NZST,/
> /           = timer_sec as utc,/
> /        &nb= sp;   hstore_to_json(string_agg(values_sec::text, ', ')::hstore)
> as values_sec/
> /    &nb= sp;from (select '$TRIP' as trip_code,/
> /   = ;               $STATION as station_no,/=
> /              &= nbsp;   date_trunc('second', timer) as timer_sec,/
&= gt; /                  values_= sec/
> /           from = t_reading_hstore_sec/
> /        &= nbsp;  where timer >=3D '$ISO_S'::timestamp - interval '12 hour'/> /             and = timer <=3D '$ISO_F'::timestamp - interval '12 hour') as foo/
> /group by timer_sec, trip_code, station_no;/
= >
> Convert the hstore to text, aggregate the text= with string_agg(),
> convert back to hstore (which s= eems to remove duplicate keys, OK for my
> purpose)
To be clear values_sec in t_reading_hst= ore_sec is the hstore field?

If so wha= t is it's structure?


> and group by timer= truncated to whole seconds. I also provide UTC &
&g= t; local timezone times for each set of readings. It is run in a bash
> script which passes the trip & station values to the= query, as well as
> the start/finish times as ISO fo= rmat strings.
>
> The output is = going to a Sqlite3 (Spatialite) database, which does not
> have hstore, or all the hstore functionality that Postgres has, but d= oes
> have a json datatype which is adequate for our = purposes, hence the
> hstore_to_json in the query.>
>
> Thanks= again,
>
> Brent Wood

>

--
Adrian Klaver
adrian.klaver@akla= ver.com




------=_Part_2625290_1461253203.1737482521072--