Date: Sat, 21 Sep 2024 03:33:55 +0000
To: Willow Chargin <postgresql@wchargin.com>, Alban Hertroys <haramrae@gmail.com>
From: Dan Kortschak <dan+pgsql@kortschak.io>
Cc: pgsql-general@lists.postgresql.org
Subject: Re: re-novice coming back to pgsql: porting an SQLite update statement to postgres
Message-ID: <8772eb2d72658c21cce576edc1cc26cd7962c895.camel@kortschak.io>
In-Reply-To: <CAALRJs65VbrcRhunsHrSn3gMnvVQ8gHc2O9Txz1RUEg6iSJo8A@mail.gmail.com>
References: <1aea530e4c96109b91fdde53a29e38beb625dc77.camel@kortschak.io> <735913e4c9601a2a7ad57d2253d74451c42ba899.camel@kortschak.io> <21078220628bcebca0395a52d42b558ed965bbc0.camel@kortschak.io> <5c86256a4e83b1af73da22faab54889564e93129.camel@kortschak.io> <7320B905-E950-4035-9A13-8C9447AB52EC@gmail.com> <48019d21fd7c669afb83161337f7e007ae619534.camel@kortschak.io> <93c6b90cd39ae5fdf49cbd698e8de0c6b6f9bf3f.camel@kortschak.io> <90CC4567-4C9E-4F83-902C-A3CE5C3C74E5@gmail.com> <CAALRJs65VbrcRhunsHrSn3gMnvVQ8gHc2O9Txz1RUEg6iSJo8A@mail.gmail.com>
Feedback-ID: 18519778:user:proton
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/8772eb2d72658c21cce576edc1cc26cd7962c895.camel%40kortschak.io>
Precedence: bulk

Thank you both. This has been extremely helpful. I still have more work
to do but this has made it possible to start playing with something,
and reading about it when it doesn't work.

On Sun, 2024-09-15 at 10:13 -0700, Willow Chargin wrote:
> On Sun, Sep 15, 2024 at 4:23=E2=80=AFAM Alban Hertroys <haramrae@gmail.co=
m>
> wrote:
> >=20
> > The drawback is that you have to specify all fields and types, but
> > you don=E2=80=99t need to cast the values all the time either.
>=20
> Here is a variant of Alban's first method that does not require
> specifying all fields and types, and so works with heterogeneous
> values:
>=20
> =C2=A0=C2=A0=C2=A0 WITH t AS (
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 SELECT=
 jsonb($$[
> =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 {"a": 1, "b": -3, "c": 1},
> =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 {"a": 2, "b": -2, "c": 2},
> =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 {"a": 3, "b": -1, "c": 3},
> =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 {"a": 3, "b": -3, "c": 4}
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 ]$$) a=
rr
> =C2=A0=C2=A0=C2=A0 )
> =C2=A0=C2=A0=C2=A0 SELECT
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 jsonb_agg(new_element ORDER BY=
 idx) new_arr
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 FROM t, LATERAL (
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 SELECT=
 idx, jsonb_object_agg(key, CASE
> =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 WHEN key =3D 'a'
> =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 THEN least(old_value::numeric=
, 2)::text::jsonb
> =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 WHEN key =3D 'b'
> =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 THEN greatest(old_value::nume=
ric, -
> 2)::text::jsonb
> =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 ELSE old_value
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 END)
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 FROM
> =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 jsonb_array_elements(arr)
> =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 WITH ORDINALITY old_elements(=
old_element, idx),
> =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 jsonb_each(old_element) each(key, old_value)
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 GROUP =
BY idx
> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 ) new_elements(idx, new_elemen=
t)
>=20
> I also took the liberties of using `least` / `greatest` to simplify
> the
> clamping operations, and using `WITH ORDINALITY` / `ORDER BY` on the
> array scan and re-aggregation to make the element ordering explicit
> rather than relying on the query engine to not re-order the rows.
>=20
> https://www.postgresql.org/docs/16/functions-conditional.html#FUNCTIONS-G=
REATEST-LEAST
> https://www.postgresql.org/docs/16/queries-table-expressions.html#QUERIES=
-TABLEFUNCTIONS

I found that I needed to old_value::text::numeric to get Willow's code
to work, but I imagine this is due to the ancientness of the postgresql
I am using.

thanks
Dan