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
On Sun, Sep 15, 2024 at 4:23 AM Alban Hertroys wrote:
>
> > On 15 Sep 2024, at 11:07, Dan Kortschak 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,
>
> On 15 Sep 2024, at 11:07, Dan Kortschak 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
>
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 14 Sep 2024, at 10:33, Dan Kortschak 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
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
> >
> > For DateTime types :
> > https://www.post
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 +
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.ht
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
On Tue, Jul 23, 2024 at 10:35 PM Adrian Klaver
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
On 7/23/24 13:11, Vincent Veyron wrote:
On Mon, 15 Jul 2024 20:31:13 +
Dan Kortschak 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
On Mon, 15 Jul 2024 20:31:13 +
Dan Kortschak 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)
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
14 matches
Mail list logo
