On Sep 11, 2024, at 10:11, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> [ looks... ] Hmm, it looks like jsonb_path_exists_tz is marked
> stable while jsonb_path_exists is claimed to be immutable.
> So yeah, there's a problem here. I'm not 100% convinced that
> jsonb_path_exists was truly immutable before, but for sure it
> is not now, and that's bad.
>
> regression=# select jsonb_path_query('"2023-08-15 12:34:56"', '$.timestamp().string()');
> jsonb_path_query
> -----------------------
> "2023-08-15 12:34:56"
> (1 row)
>
> regression=# set datestyle = postgres;
> SET
> regression=# select jsonb_path_query('"2023-08-15 12:34:56"', '$.timestamp().string()');
> jsonb_path_query
> ----------------------------
> "Tue Aug 15 12:34:56 2023"
> (1 row)
I wonder, then, whether .string() should be modified to use the ISO format in UTC, and therefore be immutable. That’s
theformat you get if you omit .string() and let result be stringified from a date/time/timestamp.
FWIW, that’s how my Go port works, since I didn’t bother to replicate the DateStyle GUC (example[1]).
Best,
David
[1]:
https://theory.github.io/sqljson/playground/?p=%2524.timestamp%28%29.string%28%29&j=%25222023-08-15%252012%253A34%253A56%2522&a=&o=1
