Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1ov12U-0007A0-Hk for pgsql-novice@arkaria.postgresql.org; Tue, 15 Nov 2022 18:52:06 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1ov12T-0003NG-3x for pgsql-novice@arkaria.postgresql.org; Tue, 15 Nov 2022 18:52:05 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1ov12S-0003N6-Ry for pgsql-novice@lists.postgresql.org; Tue, 15 Nov 2022 18:52:04 +0000 Received: from mout.gmx.net ([212.227.17.21]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1ov12Q-00023I-BX for pgsql-novice@lists.postgresql.org; Tue, 15 Nov 2022 18:52:03 +0000 Received: from mail.defcon1.lan ([93.15.31.113]) by mail.gmx.net (mrgmx104 [212.227.17.174]) with ESMTPSA (Nemesis) id 1MBUqF-1ooi371j6F-00CygX; Tue, 15 Nov 2022 19:51:58 +0100 Date: Tue, 15 Nov 2022 19:51:55 +0100 From: Bzzzz To: hubert depesz lubaczewski Cc: Laurenz Albe , pgsql-novice@lists.postgresql.org Subject: Re: hstore or jsonb ? Message-ID: <20221115195155.332eee6a@msi.defcon1.lan> In-Reply-To: References: <20221114155057.48406b86@msi.defcon1.lan> Organization: Anyone, anywhere but in banana demokratik republik of france or UERSS. Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit X-Provags-ID: V03:K1:GdioMDnA+vtw+mHfmOQrFIPhP6OE+J4vgcHuRXH7ApDesxfEnBx 964Dn94lGZxl8Q3co3zt9zu1v5JnqKHWZqruZ80/0aYvgedkhiLSD2sNeTKjEnG3nThk4tL aCu17HJfalIZBVMU5T9qE2Tknw3ZrT84mMcMZ8uOao2v/Q6rq60FbNl0pSojmmJ5g3/4yqL O4mWoyPoxx5uAX+qZsGzg== X-Spam-Flag: NO UI-OutboundReport: notjunk:1;M01:P0:4UaI8JTQZ9k=;aFTlvyaFVGdQ+pC0B3P9YjXBHkZ dEHpltdhYBRZLm9Avxy75GiwucXR61f3rsUuMZvQe1JM0jGrXgA6OKAmMjiOjzGm9dGAJuYpP XAJ4Z/yG3fLL//WdGAaMphXmGpn1Gj3P8lBS3lYahyge0XEVln+Vh2gVWJsoEeW7URCu5M99F SptZ52TYA501SCPT+NbghjFfVTAa/uXDsowbi/PMbYZ06QjXBTd8tJK0KDTVnag4lS5d1lPAP msBdMvO7bhmF2FeC5ALGBgIgSDti1PwjhEnzPURi5x5J6jcPBxsBTwSS3Zcx1+9ZlW/qlKXbS DpfXxsJ4UnS2RcQE8iOh45blYsTci15z9N2vXZ0rieINA8gqamdoL6k3Yaw6PH/rRCdApJaW5 wIFAvrj7UHG+CFdWqVvxj58Msb2Rv6Zc6moEClz19AXn543Cj7/bQrcylIo3pyBRQKz+rldT2 s6XjaFzeVD3rZ1UiuOhEndSG9Guo2GQGOwCHYF9Ub0Dyuy/BQSQX5CCeLTx20U08dIqhv7n5P FpTMsWxlO2bHcjVdrwV70Nwvy/vjGkTPbP/6dvjEIdrOH2XzAGEi+Zu5+FoChwD7CV9HRqkPs StVOxxoQxA6EYySok+sSTCY80JytKp2oyiExa+Lm5JnSlUHfGDNzchZdntPrxIOWabBXn4gDZ PbtQ66muXVfpu7b4i6D7aOfRiR6iYxzfy/GlzoQbMdrBZa54BH9nAGWkel6GCy9/T8RXrSGcm HFmU+Qg97VitZRdzu7uelcgvDs6VDlZLa+QERCIEwgpdXLW+8URtiNTNvRmr6uXF0ZhzrNX05 DeEokOlfvejs3JBi4FxLx/5mwp3BHPo/O8JWYwYoiolssqlFKg4x7xviD30OqJRNU+Q2RlIN/ /PlUkBGThiLJYkjmajeTCxEj8ZWV5SUZSGq1hWRDu7HGZ/PdPaULlzf1ieLZmezeRULaysM1E 38kXQw== List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On Tue, 15 Nov 2022 18:32:24 +0100 hubert depesz lubaczewski wrote: > > But is there anything that hstore can that JSON cannot? > > It's faster for some things: > https://www.depesz.com/2021/04/21/getting-value-from-dynamic-column-in-pl-pgsql-triggers/ Thanks for the URL, the difference is indeed quite large. > And it can be trivially used to check for differences in update > triggers: > hstore(NEW) - hstore(OLD) I didn't knew that (and it's not part of the official doc :/) > It's "abusability" is lower thanks to lack of nesting. Yeah, it is a problem as some specs can contain a list, I discovered that last night - so jsonb it'll be. Jean-Yves