re-novice coming back to pgsql: porting an SQLite update statement to postgres - Mailing list pgsql-general

The last time I used PostgreSQL for anything was about 15 years ago and
I have only limited SQL background, so please consider this a novice
question.

I have an embedded SQLite database that I would like to port to
PostgreSQL, I have done the majority of this porting, but am stuck on a
final compound statement. This is for a hobby project.

For background I'll give the SQLite schema and the statement in
question, and also the PostgreSQL schema. The SQLite code here is also
available to view in context at the links at the bottom of the post in
case this is helpful

SQLite schema:

create table if not exists buckets (
    rowid    INTEGER PRIMARY KEY AUTOINCREMENT,
    id       TEXT UNIQUE NOT NULL,
    name     TEXT,
    type     TEXT NOT NULL,
    client   TEXT NOT NULL,
    hostname TEXT NOT NULL,
    created  TEXT NOT NULL, -- unix micro
    datastr  TEXT NOT NULL  -- JSON text
);
create table if not exists events (
    id        INTEGER PRIMARY KEY AUTOINCREMENT,
    bucketrow INTEGER NOT NULL,
    starttime INTEGER NOT NULL, -- unix micro
    endtime   INTEGER NOT NULL, -- unix micro
    datastr   TEXT NOT NULL,    -- JSON text
    FOREIGN KEY (bucketrow) REFERENCES buckets(rowid)
);
create index if not exists event_index_id ON events(id);
create index if not exists event_index_starttime ON events(bucketrow, starttime);
create index if not exists event_index_endtime ON events(bucketrow, endtime);


PostgreSQL schema:

create table if not exists buckets (
    rowid    SERIAL PRIMARY KEY,
    id       TEXT UNIQUE NOT NULL,
    name     TEXT,
    type     TEXT NOT NULL,
    client   TEXT NOT NULL,
    hostname TEXT NOT NULL,
    created  TIMESTAMP WITH TIME ZONE NOT NULL,
    datastr  JSONB NOT NULL
);
create table if not exists events (
    id        SERIAL PRIMARY KEY,
    bucketrow INTEGER NOT NULL,
    starttime TIMESTAMP WITH TIME ZONE NOT NULL,
    endtime   TIMESTAMP WITH TIME ZONE NOT NULL,
    datastr   JSONB NOT NULL,
    FOREIGN KEY (bucketrow) REFERENCES buckets(rowid)
);
create index if not exists event_index_id ON events(id);
create index if not exists event_index_starttime ON events(bucketrow, starttime);
create index if not exists event_index_endtime ON events(bucketrow, endtime);


The part that I am having difficulty knowing where to start deals with
the JSON data in the events table. This contains largely unstructured
data, but can have an array of amendment notes added using the SQL
statements below. These notes are essentially append-only alterations
to the unstructured data, spanning a period of the unstructured data
within the time bounds of the event table row.

My question is where would be the best place for me to looks to learn
about how to implement a port of this SQLite? and what would broadly be
the most sensible approach to take (to narrow down what I need to read
through in learning)?

Apologies for the long post, and thank you for any help.
Dan


SQLite statement:

begin transaction;
    -- ensure we have an amend array.
    update events set datastr = json_insert(datastr, '$.amend', json('[]'))
    where
        datetime(starttime, 'subsec') < datetime(?5, 'subsec') and
        datetime(endtime, 'subsec') > datetime(?4, 'subsec') and
        bucketrow = (
            select rowid from buckets where id = ?1
        );
    update events set datastr = json_insert(datastr, '$.amend[#]', json_object('time', ?2, 'msg', ?3, 'replace', (
        -- trim amendments down to original event bounds.
        select json_group_array(json_replace(value,
            '$.start', case
                when datetime(starttime, 'subsec') > datetime(json_extract(value, '$.start'), 'subsec') then
                    starttime
                else
                    json_extract(value, '$.start')
                end,
            '$.end', case
                when datetime(endtime, 'subsec') < datetime(json_extract(value, '$.end'), 'subsec') then
                    endtime
                else
                    json_extract(value, '$.end')
                end
        ))
        from
            json_each(?6)
        where
            datetime(json_extract(value, '$.start'), 'subsec') < datetime(endtime, 'subsec') and
            datetime(json_extract(value, '$.end'), 'subsec') > datetime(starttime, 'subsec')
    )))
    where
        datetime(starttime, 'subsec') < datetime(?5, 'subsec') and
        datetime(endtime, 'subsec') > datetime(?4, 'subsec') and
        bucketrow = (
            select rowid from buckets where id = ?1
        );
commit;

Schema:
https://github.com/kortschak/dex/blob/2ce9ff385443f5f475a5efdf91721903699852fa/cmd/worklog/store/db.go#L203-L223
Amend statement:
https://github.com/kortschak/dex/blob/2ce9ff385443f5f475a5efdf91721903699852fa/cmd/worklog/store/db.go#L791-L828