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

From: Dan Kortschak <dan+pgsql(at)kortschak(dot)io>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Re: re-novice coming back to pgsql: porting an SQLite update statement to postgres
Date: 2024-09-15 09:07:53
Message-ID: 93c6b90cd39ae5fdf49cbd698e8de0c6b6f9bf3f.camel@kortschak.io
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Alban Hertroys 2024-09-15 11:22:41 Re: re-novice coming back to pgsql: porting an SQLite update statement to postgres
Previous Message Juan Rodrigo Alejandro Burgos Mella 2024-09-15 02:51:49 Re: update faster way