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

From: Vincent Veyron <[email-masked]>
Date: Tue, 23 Jul 2024 22:11:21 +0200

On Mon, 15 Jul 2024 20:31:13 +0000
Dan Kortschak <[email-masked]> 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 search the 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

---

From: Adrian Klaver <[email-masked]>
Date: Tue, 23 Jul 2024 13:35:42 -0700

> 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

-- 
Adrian Klaver
[email-masked]

---

From: Dominique Devienne <[email-masked]>
Date: Tue, 23 Jul 2024 23:52:47 +0200

On Tue, Jul 23, 2024 at 10:35 PM Adrian Klaver
<[email-masked]> 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

---

From: Dan Kortschak <[email-masked]>
Date: Wed, 24 Jul 2024 00:23:00 +0000

> https://www.postgresql.org/docs/current/datatype-json.html

Thanks, I will work through those.

> 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.

---

From: Adrian Klaver <[email-masked]>
Date: Wed, 24 Jul 2024 12:50:13 -0700

> 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.

---

From: Dan Kortschak <[email-masked]>
Date: Sat, 14 Sep 2024 08:33:33 +0000

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

---

From: Alban Hertroys <[email-masked]>
Date: Sat, 14 Sep 2024 12:05:50 +0200

> On 14 Sep 2024, at 10:33, Dan Kortschak <[email-masked]> wrote:

(…)

> the array that it shouldn't.

(…)

> select rowid from buckets where id = $1

That’s because the replacement data is an array of objects, not a single object.

You need to iterate through the array elements to build your replacement data, something like what I do here with a select (because that’s way easier to play around with):

with dollar6 as (
select jsonb($$[
                                                {
                                                        "data": { "foo": 1, "bar": 2
                                                        },
                                                        "end": "2023-06-12T19:54:51Z",
                                                        "start": "2023-06-12T19:54:39Z"
                                                }
                                        ]$$::text) replacement
)
select *                                
from dollar6
cross join lateral jsonb_array_elements(replacement) r
where (r->>'start')::timestamptz <= current_timestamp;

There are probably other ways to attack this problem, this is the one I came up with.

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.

---

From: Dan Kortschak <[email-masked]>
Date: Sat, 14 Sep 2024 10:30:08 +0000

On Sat, 2024-09-14 at 12:05 +0200, Alban Hertroys wrote:
> You need to iterate through the array elements to build your
> replacement data, something like what I do here with a select
> (because that’s way easier to play around with):
> 
> with dollar6 as (
> select jsonb($$[
>                                                 {
>                                                         "data": {
> "foo": 1, "bar": 2
>                                                         },
>                                                         "end": "2023-
> 06-12T19:54:51Z",
>                                                         "start":
> "2023-06-12T19:54:39Z"
>                                                 }
>                                         ]$$::text) replacement
> )
> select *
> from dollar6

Thanks

---

From: Dan Kortschak <[email-masked]>
Date: Sun, 15 Sep 2024 09:07:53 +0000

I have come to hopefully my last stumbling point.

I am unable to see a way to express something like this SQLite syntax

select json_group_array(json_replace(value,
  '$.a', case
    when json_extract(value, '$.a') > 2 then
      2
    else
      json_extract(value, '$.a')
    end,
  '$.b', case
    when json_extract(value, '$.b') < -2 then
      -2
    else
      json_extract(value, '$.b')
    end
))
from
  json_each('[{"a":1, "b":-3},{"a":2, "b":-2},{"a":3, "b":-1}]');

(in the repro above, the values are integers, but in the real case,
they are timestamps)

I have worked on multiple statements around the theme of 

with t as (
  select jsonb($$[{"a":1, "b":-3},{"a":2, "b":-2},{"a":3, "b":-1}]$$)
arr
)
select
  jsonb_array_elements(arr) as arr
from
  t;

The closest that I have come is 

with t as (
  select jsonb($$[{"a":1, "b":-3},{"a":2, "b":-2},{"a":3, "b":-1}]$$)
arr
)
select jsonb_set(arr, '{a}', case
  when (arr->>'a')::INTEGER > 2 then
    2
  else
    (arr->>'a')::INTEGER
  end
)
from (
  select
    jsonb_array_elements(arr) as arr
  from
    t
) elements;

but this is a millions miles from where I want to be (it doesn't work,
but I think the shape of the things that it's working with are maybe
heading in the right direction). I've read through the docs, but I just
don't seem able to get my head around this.

Any help would be greatful appreciated (also some reading direction so
that I'm not floundering so much).

thanks