Re: re-novice coming back to pgsql: porting an SQLite update statement to postgres

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-12T19:54:59.248859996+10:00", "data": {"item":1}},
{"start": "2023-06-12T19:54:50.248859996+10:00", "end": "2023-06-12T19:59:39.248859996+10:00", "data": {"item":2}},
{"start": "2023-06-12T19:56:39.248859996+10:00", "end": "2023-06-12T19:57:39.248859996+10:00", "data": {"item":3}},
{"start": "2023-06-12T19:54:39.248859996+10:00", "end": "2023-06-12T20:54:49.248859996+10:00", "data": {"item":4}},
{"start": "2024-06-12T19:54:39.248859996+10:00", "end": "2024-06-12T19: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 = 'start'
THEN to_jsonb(greatest(old::text::TIMESTAMPTZ, '2023-06-12T19:54:50+10:00'::TIMESTAMPTZ))
WHEN key = 'end'
THEN to_jsonb(least(old::text::TIMESTAMPTZ, '2023-06-12T19:58:00+10:00'::TIMESTAMPTZ))
ELSE old
END
)
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'::TIMESTAMPTZ and
(r->>'end')::TIMESTAMPTZ > '2023-06-12T19:54:50+10:00'::TIMESTAMPTZ
GROUP BY idx
) news(idx, new);