public inbox for [email protected]
help / color / mirror / Atom feedRe: re-novice coming back to pgsql: porting an SQLite update statement to postgres
9+ messages / 5 participants
[nested] [flat]
* Re: re-novice coming back to pgsql: porting an SQLite update statement to postgres
@ 2024-07-23 20:11 Vincent Veyron <[email protected]>
0 siblings, 1 reply; 9+ messages in thread
From: Vincent Veyron @ 2024-07-23 20:11 UTC (permalink / raw)
To: Dan Kortschak <[email protected]>; +Cc: pgsql-general
On Mon, 15 Jul 2024 20:31:13 +0000
Dan Kortschak <[email protected]> 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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: re-novice coming back to pgsql: porting an SQLite update statement to postgres
@ 2024-07-23 20:35 Adrian Klaver <[email protected]>
parent: Vincent Veyron <[email protected]>
0 siblings, 1 reply; 9+ messages in thread
From: Adrian Klaver @ 2024-07-23 20:35 UTC (permalink / raw)
To: Vincent Veyron <[email protected]>; Dan Kortschak <[email protected]>; +Cc: pgsql-general
On 7/23/24 13:11, Vincent Veyron wrote:
> On Mon, 15 Jul 2024 20:31:13 +0000
> Dan Kortschak <[email protected]> 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 search the archives of the pgsql-general list, many answers in there
>
>
--
Adrian Klaver
[email protected]
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: re-novice coming back to pgsql: porting an SQLite update statement to postgres
@ 2024-07-23 21:52 Dominique Devienne <[email protected]>
parent: Adrian Klaver <[email protected]>
0 siblings, 1 reply; 9+ messages in thread
From: Dominique Devienne @ 2024-07-23 21:52 UTC (permalink / raw)
To: Adrian Klaver <[email protected]>; +Cc: Vincent Veyron <[email protected]>; Dan Kortschak <[email protected]>; pgsql-general
On Tue, Jul 23, 2024 at 10:35 PM Adrian Klaver
<[email protected]> 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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: re-novice coming back to pgsql: porting an SQLite update statement to postgres
@ 2024-07-24 00:23 Dan Kortschak <[email protected]>
parent: Dominique Devienne <[email protected]>
0 siblings, 2 replies; 9+ messages in thread
From: Dan Kortschak @ 2024-07-24 00:23 UTC (permalink / raw)
To: Dominique Devienne <[email protected]>; Adrian Klaver <[email protected]>; +Cc: Vincent Veyron <[email protected]>; pgsql-general
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
> <[email protected]> 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.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: re-novice coming back to pgsql: porting an SQLite update statement to postgres
@ 2024-07-24 19:50 Adrian Klaver <[email protected]>
parent: Dan Kortschak <[email protected]>
1 sibling, 0 replies; 9+ messages in thread
From: Adrian Klaver @ 2024-07-24 19:50 UTC (permalink / raw)
To: Dan Kortschak <[email protected]>; Dominique Devienne <[email protected]>; +Cc: Vincent Veyron <[email protected]>; pgsql-general
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
>> <[email protected]> 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
[email protected]
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: re-novice coming back to pgsql: porting an SQLite update statement to postgres
@ 2024-09-14 08:33 Dan Kortschak <[email protected]>
parent: Dan Kortschak <[email protected]>
1 sibling, 1 reply; 9+ messages in thread
From: Dan Kortschak @ 2024-09-14 08:33 UTC (permalink / raw)
To: Dominique Devienne <[email protected]>; Adrian Klaver <[email protected]>; +Cc: Vincent Veyron <[email protected]>; pgsql-general
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).
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: re-novice coming back to pgsql: porting an SQLite update statement to postgres
@ 2024-09-14 10:05 Alban Hertroys <[email protected]>
parent: Dan Kortschak <[email protected]>
0 siblings, 1 reply; 9+ messages in thread
From: Alban Hertroys @ 2024-09-14 10:05 UTC (permalink / raw)
To: Dan Kortschak <[email protected]>; +Cc: Dominique Devienne <[email protected]>; Adrian Klaver <[email protected]>; Vincent Veyron <[email protected]>; pgsql-general
> On 14 Sep 2024, at 10:33, Dan Kortschak <[email protected]> wrote:
(…)
> 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.
(…)
> 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
> );
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.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: re-novice coming back to pgsql: porting an SQLite update statement to postgres
@ 2024-09-14 10:30 Dan Kortschak <[email protected]>
parent: Alban Hertroys <[email protected]>
0 siblings, 1 reply; 9+ messages in thread
From: Dan Kortschak @ 2024-09-14 10:30 UTC (permalink / raw)
To: Alban Hertroys <[email protected]>; +Cc: pgsql-general
On Sat, 2024-09-14 at 12:05 +0200, Alban Hertroys wrote:
>
> 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;
Thanks
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: re-novice coming back to pgsql: porting an SQLite update statement to postgres
@ 2024-09-15 09:07 Dan Kortschak <[email protected]>
parent: Dan Kortschak <[email protected]>
0 siblings, 0 replies; 9+ messages in thread
From: Dan Kortschak @ 2024-09-15 09:07 UTC (permalink / raw)
To: [email protected]
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
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2024-09-15 09:07 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-07-23 20:11 Re: re-novice coming back to pgsql: porting an SQLite update statement to postgres Vincent Veyron <[email protected]>
2024-07-23 20:35 ` Adrian Klaver <[email protected]>
2024-07-23 21:52 ` Dominique Devienne <[email protected]>
2024-07-24 00:23 ` Dan Kortschak <[email protected]>
2024-07-24 19:50 ` Adrian Klaver <[email protected]>
2024-09-14 08:33 ` Dan Kortschak <[email protected]>
2024-09-14 10:05 ` Alban Hertroys <[email protected]>
2024-09-14 10:30 ` Dan Kortschak <[email protected]>
2024-09-15 09:07 ` Dan Kortschak <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox