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

[Dan Kortschak]

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);