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

On Wed, 2024-07-24 at 00:23 +0000, Dan Kortschak wrote:
> On 7/23/24 13:11, Vincent Veyron wrote:
> > On Mon, 15 Jul 2024 20:31:13 +0000
> >
> > This is the goto page for anything SQL :
> > https://www.postgresql.org/docs/current/sql-commands.html
> >
> > For DateTime types :
> > https://www.postgresql.org/docs/current/datatype-datetime.html
> >
> > For JSON types :
> > https://www.postgresql.org/docs/current/datatype-json.html
>
> Thanks, I will work through those.

I've had a chance to attack this. The first part of the problem was
that I could not figure out how to get the multiple statement
transaction that I using in SQLite to work with PostgreSQL. The
solution was to use the host language's Postres binding transaction
functions and send the statements separately.

The first part, to ensure the JSON array exist is solved with

    update
        events
    set
        datastr = jsonb_set(datastr, '{amend}', '[]')
    where
        starttime < $3 and
        endtime > $2 and
        not datastr::jsonb ? 'amend' and
        bucketrow = (
            select rowid from buckets where id = $1
        );

I'm still having difficulties with the second part which is to update
the contents of the amend array in the JSON.

So far I'm able to append the relevant details to the append array, but
I'm unable to correctly select the corrects elements from the $6
argument, which is in the form
[{"start":<RFC3339>,"end":<RFC3339>,"data":<object>}, ...]. The first
update statement gives me broadly what I want, but includes elements of
the array that it shouldn't.

    update
        events
    set
        datastr = jsonb_set(
            datastr,
            '{amend}',
            datastr->'amend' || jsonb_build_object(
                'time', $2::TEXT,
                'msg', $3::TEXT,
                'replace', (
                    -- This select is for comparison with the code below.
                    select * from jsonb($6::TEXT)
                )
            )
        )
    where
        starttime < $5 and
        endtime > $4 and
        bucketrow = (
            select rowid from buckets where id = $1
        );

If I filter on the start and end time, I end up with no element coming
through at all and the "replace" field ends up null.

    update
        events
    set
        datastr = jsonb_set(
            datastr,
            '{amend}',
            datastr->'amend' || jsonb_build_object(
                'time', $2::TEXT,
                'msg', $3::TEXT,
                'replace', (
                    select *
                    from
                        jsonb($6::TEXT) as replacement
                    where
                        (replacement->>'start')::TIMESTAMP WITH TIME ZONE < endtime and
                        (replacement->>'end')::TIMESTAMP WITH TIME ZONE > starttime
                )
            )
        )
    where
        starttime < $5 and
        endtime > $4 and
        bucketrow = (
            select rowid from buckets where id = $1
        );

Can anyone suggest what I might be missing? One thing that occurs to me
is that due to the host language the timezone in starttime and endtime
is the local timezone, while the timezone in the elements of the $6
argument are in UTC. I've tried forcing the timezones to match and this
does not appear to be the issue.

This can be seen in the case of output from the first, non-filtering
update statement above:

    {
        ...
        "start": "2023-06-13T05:24:50+09:30",
        "end": "2023-06-13T05:24:55+09:30",
        "data": {
            ...
            "amend": [
                {
                    "endtime": "2023-06-13T05:24:55+09:30",
                    "msg": "testing",
                    "replace": [
                        {
                            "data": {
                                ...
                            },
                            "end": "2023-06-12T19:54:51Z",
                            "start": "2023-06-12T19:54:39Z"
                        }
                    ],
                    "starttime": "2023-06-13T05:24:50+09:30",
                    ...
                }
            ]
        }
    },

(I'm hoping there's not a dumb logic error in the statement; it's
equivalent works with SQLite, and mapped all to UTC we have
starttime=2023-06-12T19:54:50Z endtime=2023-06-12T19:54:55Z start=2023-
06-12T19:54:39Z and end=2023-06-12T19:54:51Z which is an overlap).