public inbox for [email protected]  

help / color / mirror / Atom feed

From: Dan Kortschak <[email protected]>
To: Willow Chargin <[email protected]>
To: Alban Hertroys <[email protected]>
Cc: [email protected]
Subject: Re: re-novice coming back to pgsql: porting an SQLite update statement to postgres
Date: Sat, 21 Sep 2024 03:33:55 +0000
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAALRJs65VbrcRhunsHrSn3gMnvVQ8gHc2O9Txz1RUEg6iSJo8A@mail.gmail.com>
References: <[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<CAALRJs65VbrcRhunsHrSn3gMnvVQ8gHc2O9Txz1RUEg6iSJo8A@mail.gmail.com>

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 AM Alban Hertroys <[email protected]>
> wrote:
> > 
> > The drawback is that you have to specify all fields and types, but
> > you don’t need to cast the values all the time either.
> 
> Here is a variant of Alban's first method that does not require
> specifying all fields and types, and so works with heterogeneous
> values:
> 
>     WITH t AS (
>             SELECT jsonb($$[
>                 {"a": 1, "b": -3, "c": 1},
>                 {"a": 2, "b": -2, "c": 2},
>                 {"a": 3, "b": -1, "c": 3},
>                 {"a": 3, "b": -3, "c": 4}
>             ]$$) arr
>     )
>     SELECT
>         jsonb_agg(new_element ORDER BY idx) new_arr
>         FROM t, LATERAL (
>             SELECT idx, jsonb_object_agg(key, CASE
>                 WHEN key = 'a'
>                     THEN least(old_value::numeric, 2)::text::jsonb
>                 WHEN key = 'b'
>                     THEN greatest(old_value::numeric, -
> 2)::text::jsonb
>                 ELSE old_value
>             END)
>             FROM
>                 jsonb_array_elements(arr)
>                     WITH ORDINALITY old_elements(old_element, idx),
>                 jsonb_each(old_element) each(key, old_value)
>             GROUP BY idx
>         ) new_elements(idx, new_element)
> 
> 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.
> 
> https://www.postgresql.org/docs/16/functions-conditional.html#FUNCTIONS-GREATEST-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

view thread (4+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected]
  Subject: Re: re-novice coming back to pgsql: porting an SQLite update statement to postgres
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox