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 06:52:36 +0000
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<CAALRJs65VbrcRhunsHrSn3gMnvVQ8gHc2O9Txz1RUEg6iSJo8A@mail.gmail.com>
	<[email protected]>

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

view thread (4+ messages)

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