Date: Sat, 21 Sep 2024 06:52:36 +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: <ca4e39816e390f51f382ed4f37403640a9ae8347.camel@kortschak.io>
In-Reply-To: <8772eb2d72658c21cce576edc1cc26cd7962c895.camel@kortschak.io>
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> <8772eb2d72658c21cce576edc1cc26cd7962c895.camel@kortschak.io>
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/ca4e39816e390f51f382ed4f37403640a9ae8347.camel%40kortschak.io>
Precedence: bulk

This is the toy with the shape of data that will be seen in the
application. The final trick was to use to_jsonb to allow the
timestamptz to be put back into the jsonb.

WITH replace AS (
    SELECT jsonb($$[
        {"start": "2023-06-12T19:54:39.248859996+10:00", "end": "2023-06-12=
T19:54:59.248859996+10:00", "data": {"item":1}},
        {"start": "2023-06-12T19:54:50.248859996+10:00", "end": "2023-06-12=
T19:59:39.248859996+10:00", "data": {"item":2}},
        {"start": "2023-06-12T19:56:39.248859996+10:00", "end": "2023-06-12=
T19:57:39.248859996+10:00", "data": {"item":3}},
        {"start": "2023-06-12T19:54:39.248859996+10:00", "end": "2023-06-12=
T20:54:49.248859996+10:00", "data": {"item":4}},
        {"start": "2024-06-12T19:54:39.248859996+10:00", "end": "2024-06-12=
T19:59:39.248859996+10:00", "data": {"item":5}}
    ]$$) replacements
)
SELECT
    jsonb_agg(new ORDER BY idx) trimmed_replacements
FROM
    replace, LATERAL (
        SELECT idx, jsonb_object_agg(key,
            CASE
                WHEN key =3D 'start'
                    THEN to_jsonb(greatest(old::text::TIMESTAMPTZ, '2023-06=
-12T19:54:50+10:00'::TIMESTAMPTZ))
                WHEN key =3D 'end'
                    THEN to_jsonb(least(old::text::TIMESTAMPTZ, '2023-06-12=
T19:58:00+10:00'::TIMESTAMPTZ))
                ELSE old
            END=20
        )
        FROM
            jsonb_array_elements(replacements)
                WITH ORDINALITY rs(r, idx),
            jsonb_each(r) each(key, old)
        WHERE
            (r->>'start')::TIMESTAMPTZ < '2023-06-12T19:58:00+10:00'::TIMES=
TAMPTZ and=20
            (r->>'end')::TIMESTAMPTZ > '2023-06-12T19:54:50+10:00'::TIMESTA=
MPTZ
        GROUP BY idx
    ) news(idx, new);