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

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

On Mon, 15 Jul 2024 20:31:13 +0000
Dan Kortschak <dan+pgsql@kortschak.io> wrote:

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

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

If your query works in SQLite, all you have to do is read those, and try to port; if it fails, read them again. Also
searchthe archives of the pgsql-general list, many answers in there 


--
                    Bien à vous, Vincent Veyron

https://compta.libremen.com
Logiciel libre de comptabilité générale en partie double

On 7/23/24 13:11, Vincent Veyron wrote:
> On Mon, 15 Jul 2024 20:31:13 +0000
> Dan Kortschak <dan+pgsql@kortschak.io> wrote:
> 
>> 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)?
>>
> 
> 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

Just know that SQLite does not enforce types, therefore it is entirely 
possible that there are values in fields that are not valid in Postgres.

Think:

select ''::integer
ERROR:  invalid input syntax for type integer: ""
LINE 1: select ''::integer


> 
> If your query works in SQLite, all you have to do is read those, and try to port; if it fails, read them again. Also
searchthe archives of the pgsql-general list, many answers in there
 
> 
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com

On Tue, Jul 23, 2024 at 10:35 PM Adrian Klaver
<adrian.klaver@aklaver.com> wrote:
> Just know that SQLite does not enforce types [...]

That's true, and applies to the OP's schema.

But for the record, SQLite *can* enforce types these days,
on an opt-in basis, with [STRICT tables][1].
Albeit with a limited type-system. --DD

PS: and could be done manually even before, with CHECK
(typeof(col)='blob') for example.

[1]: https://www.sqlite.org/stricttables.html

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.


On Tue, 2024-07-23 at 23:52 +0200, Dominique Devienne wrote:
> On Tue, Jul 23, 2024 at 10:35 PM Adrian Klaver
> <adrian.klaver@aklaver.com> wrote:
> > Just know that SQLite does not enforce types [...]
>
> That's true, and applies to the OP's schema.

Thank you both. Yes, I was aware of this weirdness of the schema (I
inherited it) and was shocked that it worked when I relaised. I'll be
happier when types are properly enforced, but I don't think I can
retrospectively enforce that on the SQLite implementation I have.

On 7/23/24 17:23, 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.
> 
> 
> On Tue, 2024-07-23 at 23:52 +0200, Dominique Devienne wrote:
>> On Tue, Jul 23, 2024 at 10:35 PM Adrian Klaver
>> <adrian.klaver@aklaver.com> wrote:
>>> Just know that SQLite does not enforce types [...]
>>
>> That's true, and applies to the OP's schema.
> 
> Thank you both. Yes, I was aware of this weirdness of the schema (I
> inherited it) and was shocked that it worked when I relaised. I'll be
> happier when types are properly enforced, but I don't think I can
> retrospectively enforce that on the SQLite implementation I have.
> 

Which gets back to verifying the data coming from SQLite will work in 
the Postgres tables with the Postgres types specified in the table 
definitions.

You can either:

1) Just import the data into the Postgres tables as defined and see if 
it works and if not what blows up.

2) Create Postgres staging tables that have all the column type's set to 
varchar or text for every column. Then import the data. Then you could 
do select col::<the_type_desired> from the_table and see what works and 
what fails.

-- 
Adrian Klaver
adrian.klaver@aklaver.com