This is the toy with the shape of data that will be seen in the
application. The final trick was to use to_jsonb to allow the
timestamptz to be put back into the jsonb.
WITH replace AS (
SELECT jsonb($$[
{"start": "2023-06-12T19:54:39.248859996+10:00", "end":
"2023-06-12T19:54:59.2488
Thank you both. This has been extremely helpful. I still have more work
to do but this has made it possible to start playing with something,
and reading about it when it doesn't work.
On Sun, 2024-09-15 at 10:13 -0700, Willow Chargin wrote:
> On Sun, Sep 15, 2024 at 4:23 AM Alban Hertroys
> wrote
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
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 ea
On Wed, 2024-07-24 at 00:23 +, Dan Kortschak wrote:
> On 7/23/24 13:11, Vincent Veyron wrote:
> > On Mon, 15 Jul 2024 20:31:13 +
> >
> > This is the goto page for anything SQL :
> > https://www.postgresql.org/docs/current/sql-commands.html
> >
&
On 7/23/24 13:11, Vincent Veyron wrote:
> On Mon, 15 Jul 2024 20:31:13 +
>
> 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 :
> http
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 compo