recently added jsonpath method change jsonb_path_query, jsonb_path_query_first immutability
Started by jian healmost 2 years ago9 messages
Hi.
this commit [0]https://git.postgresql.org/cgit/postgresql.git/commit/?id=66ea94e8e606529bb334515f388c62314956739e changes immutability of jsonb_path_query,
jsonb_path_query_first?
If so, it may change other functions also.
demo:
begin;
SET LOCAL TIME ZONE 10.5;
with cte(s) as (select jsonb '"2023-08-15 12:34:56 +05:30"')
select jsonb_path_query(s,
'$.timestamp_tz()')::text,'+10.5'::text,'timestamp_tz'::text from cte
union all
select jsonb_path_query(s, '$.time()')::text,'+10.5'::text, 'time'::text
from cte
union all
select jsonb_path_query(s,
'$.timestamp()')::text,'+10.5'::text,'timestamp'::text from cte
union all
select jsonb_path_query(s, '$.date()')::text,'+10.5'::text, 'date'::text
from cte
union all
select jsonb_path_query(s, '$.time_tz()')::text,'+10.5'::text,
'time_tz'::text from cte;
SET LOCAL TIME ZONE -8;
with cte(s) as (select jsonb '"2023-08-15 12:34:56 +05:30"')
select jsonb_path_query(s,
'$.timestamp_tz()')::text,'-8'::text,'timestamp_tz'::text from cte
union all
select jsonb_path_query(s, '$.time()')::text,'-8'::text, 'time'::text from
cte
union all
select jsonb_path_query(s,
'$.timestamp()')::text,'-8'::text,'timestamp'::text from cte
union all
select jsonb_path_query(s, '$.date()')::text,'-8'::text, 'date'::text from
cte
union all
select jsonb_path_query(s, '$.time_tz()')::text,'-8'::text, 'time_tz'::text
from cte;
commit;
[0]: https://git.postgresql.org/cgit/postgresql.git/commit/?id=66ea94e8e606529bb334515f388c62314956739e
https://git.postgresql.org/cgit/postgresql.git/commit/?id=66ea94e8e606529bb334515f388c62314956739e
Re: recently added jsonpath method change jsonb_path_query, jsonb_path_query_first immutability
On 2024-02-05 Mo 22:06, jian he wrote:
Hi.
this commit [0] changes immutability of jsonb_path_query,
jsonb_path_query_first? If so, it may change other functions also.demo:
begin;
SET LOCAL TIME ZONE 10.5;
with cte(s) as (select jsonb '"2023-08-15 12:34:56 +05:30"')select jsonb_path_query(s,
'$.timestamp_tz()')::text,'+10.5'::text,'timestamp_tz'::text from cte
union all
select jsonb_path_query(s, '$.time()')::text,'+10.5'::text,
'time'::text from cte
union all
select jsonb_path_query(s,
'$.timestamp()')::text,'+10.5'::text,'timestamp'::text from cte
union all
select jsonb_path_query(s, '$.date()')::text,'+10.5'::text,
'date'::text from cte
union all
select jsonb_path_query(s, '$.time_tz()')::text,'+10.5'::text,
'time_tz'::text from cte;SET LOCAL TIME ZONE -8;
with cte(s) as (select jsonb '"2023-08-15 12:34:56 +05:30"')
select jsonb_path_query(s,
'$.timestamp_tz()')::text,'-8'::text,'timestamp_tz'::text from cte
union all
select jsonb_path_query(s, '$.time()')::text,'-8'::text, 'time'::text
from cte
union all
select jsonb_path_query(s,
'$.timestamp()')::text,'-8'::text,'timestamp'::text from cte
union all
select jsonb_path_query(s, '$.date()')::text,'-8'::text, 'date'::text
from cte
union all
select jsonb_path_query(s, '$.time_tz()')::text,'-8'::text,
'time_tz'::text from cte;
commit;[0]
https://git.postgresql.org/cgit/postgresql.git/commit/?id=66ea94e8e606529bb334515f388c62314956739e
ouch. Good catch. Clearly we need to filter these like we do for the
.datetime() method.
cheers
andrew
--
Andrew Dunstan
EDB:https://www.enterprisedb.com
Re: recently added jsonpath method change jsonb_path_query, jsonb_path_query_first immutability
On Tue, Feb 6, 2024 at 5:25 PM Andrew Dunstan <andrew@dunslane.net> wrote:
On 2024-02-05 Mo 22:06, jian he wrote:
Hi.
this commit [0] changes immutability of jsonb_path_query, jsonb_path_query_first?
If so, it may change other functions also.
Thanks for reporting Jian.
Added checkTimezoneIsUsedForCast() check where ever we are casting
timezoned to non-timezoned types and vice-versa.
Thanks
demo:
begin;
SET LOCAL TIME ZONE 10.5;
with cte(s) as (select jsonb '"2023-08-15 12:34:56 +05:30"')select jsonb_path_query(s,
'$.timestamp_tz()')::text,'+10.5'::text,'timestamp_tz'::text from cte
union all
select jsonb_path_query(s, '$.time()')::text,'+10.5'::text, 'time'::text
from cte
union all
select jsonb_path_query(s,
'$.timestamp()')::text,'+10.5'::text,'timestamp'::text from cte
union all
select jsonb_path_query(s, '$.date()')::text,'+10.5'::text, 'date'::text
from cte
union all
select jsonb_path_query(s, '$.time_tz()')::text,'+10.5'::text,
'time_tz'::text from cte;SET LOCAL TIME ZONE -8;
with cte(s) as (select jsonb '"2023-08-15 12:34:56 +05:30"')
select jsonb_path_query(s,
'$.timestamp_tz()')::text,'-8'::text,'timestamp_tz'::text from cte
union all
select jsonb_path_query(s, '$.time()')::text,'-8'::text, 'time'::text from
cte
union all
select jsonb_path_query(s,
'$.timestamp()')::text,'-8'::text,'timestamp'::text from cte
union all
select jsonb_path_query(s, '$.date()')::text,'-8'::text, 'date'::text from
cte
union all
select jsonb_path_query(s, '$.time_tz()')::text,'-8'::text,
'time_tz'::text from cte;
commit;[0]
https://git.postgresql.org/cgit/postgresql.git/commit/?id=66ea94e8e606529bb334515f388c62314956739eouch. Good catch. Clearly we need to filter these like we do for the
.datetime() method.cheers
andrew
--
Andrew Dunstan
EDB: https://www.enterprisedb.com
--
Jeevan Chalke
*Principal, ManagerProduct Development*
edbpostgres.com
Attachments:
preserve-immutability.patchapplication/octet-stream; name=preserve-immutability.patchDownload
commit b8314bc2970da80b56c4b98e44599ffe0e9663fa
Author: Jeevan Chalke <jeevan.chalke@enterprisedb.com>
Date: Wed Feb 7 17:01:25 2024 +0530
Preserve immutability in the date-time methods involving casting.
Timezones themselves are not immutable and could be updated. In
commit 66ea94e8e606529bb334515f388c62314956739e, we have introduced
a few methods which does the casting from one time to another and
may involve the current timezone. To preserve the immutability,
disallow them from being called from non-TZ aware function.
Jeevan Chalke, per a report from Jian He.
diff --git a/src/backend/utils/adt/jsonpath_exec.c b/src/backend/utils/adt/jsonpath_exec.c
index 573b6ce..8372863 100644
--- a/src/backend/utils/adt/jsonpath_exec.c
+++ b/src/backend/utils/adt/jsonpath_exec.c
@@ -268,6 +268,8 @@ static JsonbValue *getScalar(JsonbValue *scalar, enum jbvType type);
static JsonbValue *wrapItemsInArray(const JsonValueList *items);
static int compareDatetime(Datum val1, Oid typid1, Datum val2, Oid typid2,
bool useTz, bool *cast_error);
+static void checkTimezoneIsUsedForCast(bool useTz, const char *type1,
+ const char *type2);
/****************** User interface to JsonPath executor ********************/
@@ -2409,6 +2411,8 @@ executeDateTimeMethod(JsonPathExecContext *cxt, JsonPathItem *jsp,
value);
break;
case TIMESTAMPTZOID:
+ checkTimezoneIsUsedForCast(cxt->useTz,
+ "timestamptz", "date");
value = DirectFunctionCall1(timestamptz_date,
value);
break;
@@ -2433,6 +2437,8 @@ executeDateTimeMethod(JsonPathExecContext *cxt, JsonPathItem *jsp,
case TIMEOID: /* Nothing to do for TIME */
break;
case TIMETZOID:
+ checkTimezoneIsUsedForCast(cxt->useTz,
+ "timetz", "time");
value = DirectFunctionCall1(timetz_time,
value);
break;
@@ -2441,6 +2447,8 @@ executeDateTimeMethod(JsonPathExecContext *cxt, JsonPathItem *jsp,
value);
break;
case TIMESTAMPTZOID:
+ checkTimezoneIsUsedForCast(cxt->useTz,
+ "timestamptz", "time");
value = DirectFunctionCall1(timestamptz_time,
value);
break;
@@ -2480,6 +2488,8 @@ executeDateTimeMethod(JsonPathExecContext *cxt, JsonPathItem *jsp,
text_to_cstring(datetime)))));
break;
case TIMEOID:
+ checkTimezoneIsUsedForCast(cxt->useTz,
+ "time", "timetz");
value = DirectFunctionCall1(time_timetz,
value);
break;
@@ -2531,6 +2541,8 @@ executeDateTimeMethod(JsonPathExecContext *cxt, JsonPathItem *jsp,
case TIMESTAMPOID: /* Nothing to do for TIMESTAMP */
break;
case TIMESTAMPTZOID:
+ checkTimezoneIsUsedForCast(cxt->useTz,
+ "timestamptz", "timestamp");
value = DirectFunctionCall1(timestamptz_timestamp,
value);
break;
@@ -2570,6 +2582,8 @@ executeDateTimeMethod(JsonPathExecContext *cxt, JsonPathItem *jsp,
switch (typid)
{
case DATEOID:
+ checkTimezoneIsUsedForCast(cxt->useTz,
+ "date", "timestamptz");
value = DirectFunctionCall1(date_timestamptz,
value);
break;
@@ -2581,6 +2595,8 @@ executeDateTimeMethod(JsonPathExecContext *cxt, JsonPathItem *jsp,
text_to_cstring(datetime)))));
break;
case TIMESTAMPOID:
+ checkTimezoneIsUsedForCast(cxt->useTz,
+ "timestamp", "timestamptz");
value = DirectFunctionCall1(timestamp_timestamptz,
value);
break;
diff --git a/src/test/regress/expected/jsonb_jsonpath.out b/src/test/regress/expected/jsonb_jsonpath.out
index eea2af3..414c296 100644
--- a/src/test/regress/expected/jsonb_jsonpath.out
+++ b/src/test/regress/expected/jsonb_jsonpath.out
@@ -2088,8 +2088,11 @@ select jsonb_path_query('"2023-08-15 12:34:56"', '$.date()');
(1 row)
select jsonb_path_query('"2023-08-15 12:34:56 +05:30"', '$.date()');
- jsonb_path_query
-------------------
+ERROR: cannot convert value from timestamptz to date without time zone usage
+HINT: Use *_tz() function for time zone support.
+select jsonb_path_query_tz('"2023-08-15 12:34:56 +05:30"', '$.date()'); -- should work
+ jsonb_path_query_tz
+---------------------
"2023-08-15"
(1 row)
@@ -2574,7 +2577,10 @@ select jsonb_path_query('1234', '$.string().type()');
(1 row)
select jsonb_path_query('"2023-08-15 12:34:56 +5:30"', '$.timestamp().string()');
- jsonb_path_query
+ERROR: cannot convert value from timestamptz to timestamp without time zone usage
+HINT: Use *_tz() function for time zone support.
+select jsonb_path_query_tz('"2023-08-15 12:34:56 +5:30"', '$.timestamp().string()'); -- should work
+ jsonb_path_query_tz
----------------------------
"Tue Aug 15 00:04:56 2023"
(1 row)
@@ -2630,8 +2636,11 @@ select jsonb_path_query('"12:34:56"', '$.time().type()');
select jsonb_path_query('"2023-08-15"', '$.time()');
ERROR: time format is not recognized: "2023-08-15"
select jsonb_path_query('"12:34:56 +05:30"', '$.time()');
- jsonb_path_query
-------------------
+ERROR: cannot convert value from timetz to time without time zone usage
+HINT: Use *_tz() function for time zone support.
+select jsonb_path_query_tz('"12:34:56 +05:30"', '$.time()'); -- should work
+ jsonb_path_query_tz
+---------------------
"12:34:56"
(1 row)
@@ -2890,7 +2899,10 @@ select jsonb_path_query('"2023-08-15 12:34:56 +05:30"', '$.timestamp_tz().type()
(1 row)
select jsonb_path_query('"2023-08-15"', '$.timestamp_tz()');
- jsonb_path_query
+ERROR: cannot convert value from date to timestamptz without time zone usage
+HINT: Use *_tz() function for time zone support.
+select jsonb_path_query_tz('"2023-08-15"', '$.timestamp_tz()'); -- should work
+ jsonb_path_query_tz
-----------------------------
"2023-08-15T07:00:00+00:00"
(1 row)
@@ -2943,8 +2955,11 @@ WARNING: TIMESTAMP(8) WITH TIME ZONE precision reduced to maximum allowed, 6
set time zone '+00';
select jsonb_path_query('"2023-08-15 12:34:56 +05:30"', '$.time()');
- jsonb_path_query
-------------------
+ERROR: cannot convert value from timestamptz to time without time zone usage
+HINT: Use *_tz() function for time zone support.
+select jsonb_path_query_tz('"2023-08-15 12:34:56 +05:30"', '$.time()'); -- should work
+ jsonb_path_query_tz
+---------------------
"07:04:56"
(1 row)
@@ -2955,19 +2970,28 @@ select jsonb_path_query('"2023-08-15 12:34:56 +05:30"', '$.time_tz()');
(1 row)
select jsonb_path_query('"12:34:56"', '$.time_tz()');
- jsonb_path_query
-------------------
+ERROR: cannot convert value from time to timetz without time zone usage
+HINT: Use *_tz() function for time zone support.
+select jsonb_path_query_tz('"12:34:56"', '$.time_tz()'); -- should work
+ jsonb_path_query_tz
+---------------------
"12:34:56+00:00"
(1 row)
select jsonb_path_query('"2023-08-15 12:34:56 +05:30"', '$.timestamp()');
- jsonb_path_query
+ERROR: cannot convert value from timestamptz to timestamp without time zone usage
+HINT: Use *_tz() function for time zone support.
+select jsonb_path_query_tz('"2023-08-15 12:34:56 +05:30"', '$.timestamp()'); -- should work
+ jsonb_path_query_tz
-----------------------
"2023-08-15T07:04:56"
(1 row)
select jsonb_path_query('"2023-08-15 12:34:56"', '$.timestamp_tz()');
- jsonb_path_query
+ERROR: cannot convert value from timestamp to timestamptz without time zone usage
+HINT: Use *_tz() function for time zone support.
+select jsonb_path_query_tz('"2023-08-15 12:34:56"', '$.timestamp_tz()'); -- should work
+ jsonb_path_query_tz
-----------------------------
"2023-08-15T12:34:56+00:00"
(1 row)
@@ -3038,8 +3062,11 @@ select jsonb_path_query('"12:34 -05:20"', '$.datetime("HH24:MI TZH:TZM")');
set time zone '+10';
select jsonb_path_query('"2023-08-15 12:34:56 +05:30"', '$.time()');
- jsonb_path_query
-------------------
+ERROR: cannot convert value from timestamptz to time without time zone usage
+HINT: Use *_tz() function for time zone support.
+select jsonb_path_query_tz('"2023-08-15 12:34:56 +05:30"', '$.time()'); -- should work
+ jsonb_path_query_tz
+---------------------
"17:04:56"
(1 row)
@@ -3050,13 +3077,19 @@ select jsonb_path_query('"2023-08-15 12:34:56 +05:30"', '$.time_tz()');
(1 row)
select jsonb_path_query('"2023-08-15 12:34:56 +05:30"', '$.timestamp()');
- jsonb_path_query
+ERROR: cannot convert value from timestamptz to timestamp without time zone usage
+HINT: Use *_tz() function for time zone support.
+select jsonb_path_query_tz('"2023-08-15 12:34:56 +05:30"', '$.timestamp()'); -- should work
+ jsonb_path_query_tz
-----------------------
"2023-08-15T17:04:56"
(1 row)
select jsonb_path_query('"2023-08-15 12:34:56"', '$.timestamp_tz()');
- jsonb_path_query
+ERROR: cannot convert value from timestamp to timestamptz without time zone usage
+HINT: Use *_tz() function for time zone support.
+select jsonb_path_query_tz('"2023-08-15 12:34:56"', '$.timestamp_tz()'); -- should work
+ jsonb_path_query_tz
-----------------------------
"2023-08-15T02:34:56+00:00"
(1 row)
@@ -3133,8 +3166,11 @@ select jsonb_path_query('"12:34 -05:20"', '$.datetime("HH24:MI TZH:TZM")');
set time zone default;
select jsonb_path_query('"2023-08-15 12:34:56 +05:30"', '$.time()');
- jsonb_path_query
-------------------
+ERROR: cannot convert value from timestamptz to time without time zone usage
+HINT: Use *_tz() function for time zone support.
+select jsonb_path_query_tz('"2023-08-15 12:34:56 +05:30"', '$.time()'); -- should work
+ jsonb_path_query_tz
+---------------------
"00:04:56"
(1 row)
@@ -3145,7 +3181,10 @@ select jsonb_path_query('"2023-08-15 12:34:56 +05:30"', '$.time_tz()');
(1 row)
select jsonb_path_query('"2023-08-15 12:34:56 +05:30"', '$.timestamp()');
- jsonb_path_query
+ERROR: cannot convert value from timestamptz to timestamp without time zone usage
+HINT: Use *_tz() function for time zone support.
+select jsonb_path_query_tz('"2023-08-15 12:34:56 +05:30"', '$.timestamp()'); -- should work
+ jsonb_path_query_tz
-----------------------
"2023-08-15T00:04:56"
(1 row)
@@ -3358,35 +3397,18 @@ select jsonb_path_query_tz(
select jsonb_path_query(
'["2017-03-10", "2017-03-11", "2017-03-09", "2017-03-10 00:00:00", "2017-03-10 12:34:56", "2017-03-10 01:02:03+04", "2017-03-10 03:00:00+03"]',
'$[*].date() ? (@ == "2017-03-10".date())');
- jsonb_path_query
-------------------
- "2017-03-10"
- "2017-03-10"
- "2017-03-10"
- "2017-03-10"
-(4 rows)
-
+ERROR: cannot convert value from timestamptz to date without time zone usage
+HINT: Use *_tz() function for time zone support.
select jsonb_path_query(
'["2017-03-10", "2017-03-11", "2017-03-09", "2017-03-10 00:00:00", "2017-03-10 12:34:56", "2017-03-10 01:02:03+04", "2017-03-10 03:00:00+03"]',
'$[*].date() ? (@ >= "2017-03-10".date())');
- jsonb_path_query
-------------------
- "2017-03-10"
- "2017-03-11"
- "2017-03-10"
- "2017-03-10"
- "2017-03-10"
-(5 rows)
-
+ERROR: cannot convert value from timestamptz to date without time zone usage
+HINT: Use *_tz() function for time zone support.
select jsonb_path_query(
'["2017-03-10", "2017-03-11", "2017-03-09", "2017-03-10 00:00:00", "2017-03-10 12:34:56", "2017-03-10 01:02:03+04", "2017-03-10 03:00:00+03"]',
'$[*].date() ? (@ < "2017-03-10".date())');
- jsonb_path_query
-------------------
- "2017-03-09"
- "2017-03-09"
-(2 rows)
-
+ERROR: cannot convert value from timestamptz to date without time zone usage
+HINT: Use *_tz() function for time zone support.
select jsonb_path_query_tz(
'["2017-03-10", "2017-03-11", "2017-03-09", "2017-03-10 00:00:00", "2017-03-10 12:34:56", "2017-03-10 01:02:03+04", "2017-03-10 03:00:00+03"]',
'$[*].date() ? (@ == "2017-03-10".date())');
@@ -3496,36 +3518,23 @@ select jsonb_path_query_tz(
select jsonb_path_query(
'["12:34:00", "12:35:00", "12:36:00", "12:35:00+00", "12:35:00+01", "13:35:00+01", "2017-03-10 12:35:00", "2017-03-10 12:35:00+01"]',
'$[*].time() ? (@ == "12:35:00".time())');
- jsonb_path_query
-------------------
- "12:35:00"
- "12:35:00"
- "12:35:00"
- "12:35:00"
-(4 rows)
-
+ERROR: cannot convert value from timetz to time without time zone usage
+HINT: Use *_tz() function for time zone support.
select jsonb_path_query(
'["12:34:00", "12:35:00", "12:36:00", "12:35:00+00", "12:35:00+01", "13:35:00+01", "2017-03-10 12:35:00", "2017-03-10 12:35:00+01"]',
'$[*].time() ? (@ >= "12:35:00".time())');
- jsonb_path_query
-------------------
- "12:35:00"
- "12:36:00"
- "12:35:00"
- "12:35:00"
- "13:35:00"
- "12:35:00"
-(6 rows)
-
+ERROR: cannot convert value from timetz to time without time zone usage
+HINT: Use *_tz() function for time zone support.
select jsonb_path_query(
'["12:34:00", "12:35:00", "12:36:00", "12:35:00+00", "12:35:00+01", "13:35:00+01", "2017-03-10 12:35:00", "2017-03-10 12:35:00+01"]',
'$[*].time() ? (@ < "12:35:00".time())');
- jsonb_path_query
-------------------
- "12:34:00"
- "11:35:00"
-(2 rows)
-
+ERROR: cannot convert value from timetz to time without time zone usage
+HINT: Use *_tz() function for time zone support.
+select jsonb_path_query(
+ '["12:34:00.123", "12:35:00.123", "12:36:00.1123", "12:35:00.1123+00", "12:35:00.123+01", "13:35:00.123+01", "2017-03-10 12:35:00.1", "2017-03-10 12:35:00.123+01"]',
+ '$[*].time(2) ? (@ >= "12:35:00.123".time(2))');
+ERROR: cannot convert value from timetz to time without time zone usage
+HINT: Use *_tz() function for time zone support.
select jsonb_path_query_tz(
'["12:34:00", "12:35:00", "12:36:00", "12:35:00+00", "12:35:00+01", "13:35:00+01", "2017-03-10 12:35:00", "2017-03-10 12:35:00+01"]',
'$[*].time() ? (@ == "12:35:00".time())');
@@ -3559,11 +3568,11 @@ select jsonb_path_query_tz(
"11:35:00"
(2 rows)
-select jsonb_path_query(
+select jsonb_path_query_tz(
'["12:34:00.123", "12:35:00.123", "12:36:00.1123", "12:35:00.1123+00", "12:35:00.123+01", "13:35:00.123+01", "2017-03-10 12:35:00.1", "2017-03-10 12:35:00.123+01"]',
'$[*].time(2) ? (@ >= "12:35:00.123".time(2))');
- jsonb_path_query
-------------------
+ jsonb_path_query_tz
+---------------------
"12:35:00.12"
"12:36:00.11"
"12:35:00.12"
@@ -3649,34 +3658,23 @@ select jsonb_path_query_tz(
select jsonb_path_query(
'["12:34:00+01", "12:35:00+01", "12:36:00+01", "12:35:00+02", "12:35:00-02", "10:35:00", "11:35:00", "12:35:00", "2017-03-10 12:35:00 +1"]',
'$[*].time_tz() ? (@ == "12:35:00 +1".time_tz())');
- jsonb_path_query
-------------------
- "12:35:00+01:00"
-(1 row)
-
+ERROR: cannot convert value from time to timetz without time zone usage
+HINT: Use *_tz() function for time zone support.
select jsonb_path_query(
'["12:34:00+01", "12:35:00+01", "12:36:00+01", "12:35:00+02", "12:35:00-02", "10:35:00", "11:35:00", "12:35:00", "2017-03-10 12:35:00 +1"]',
'$[*].time_tz() ? (@ >= "12:35:00 +1".time_tz())');
- jsonb_path_query
-------------------
- "12:35:00+01:00"
- "12:36:00+01:00"
- "12:35:00-02:00"
- "11:35:00+00:00"
- "12:35:00+00:00"
- "11:35:00+00:00"
-(6 rows)
-
+ERROR: cannot convert value from time to timetz without time zone usage
+HINT: Use *_tz() function for time zone support.
select jsonb_path_query(
'["12:34:00+01", "12:35:00+01", "12:36:00+01", "12:35:00+02", "12:35:00-02", "10:35:00", "11:35:00", "12:35:00", "2017-03-10 12:35:00 +1"]',
'$[*].time_tz() ? (@ < "12:35:00 +1".time_tz())');
- jsonb_path_query
-------------------
- "12:34:00+01:00"
- "12:35:00+02:00"
- "10:35:00+00:00"
-(3 rows)
-
+ERROR: cannot convert value from time to timetz without time zone usage
+HINT: Use *_tz() function for time zone support.
+select jsonb_path_query(
+ '["12:34:00.123+01", "12:35:00.123+01", "12:36:00.1123+01", "12:35:00.1123+02", "12:35:00.123-02", "10:35:00.123", "11:35:00.1", "12:35:00.123", "2017-03-10 12:35:00.123 +1"]',
+ '$[*].time_tz(2) ? (@ >= "12:35:00.123 +1".time_tz(2))');
+ERROR: cannot convert value from time to timetz without time zone usage
+HINT: Use *_tz() function for time zone support.
select jsonb_path_query_tz(
'["12:34:00+01", "12:35:00+01", "12:36:00+01", "12:35:00+02", "12:35:00-02", "10:35:00", "11:35:00", "12:35:00", "2017-03-10 12:35:00 +1"]',
'$[*].time_tz() ? (@ == "12:35:00 +1".time_tz())');
@@ -3708,10 +3706,10 @@ select jsonb_path_query_tz(
"10:35:00+00:00"
(3 rows)
-select jsonb_path_query(
+select jsonb_path_query_tz(
'["12:34:00.123+01", "12:35:00.123+01", "12:36:00.1123+01", "12:35:00.1123+02", "12:35:00.123-02", "10:35:00.123", "11:35:00.1", "12:35:00.123", "2017-03-10 12:35:00.123 +1"]',
'$[*].time_tz(2) ? (@ >= "12:35:00.123 +1".time_tz(2))');
- jsonb_path_query
+ jsonb_path_query_tz
---------------------
"12:35:00.12+01:00"
"12:36:00.11+01:00"
@@ -3801,34 +3799,23 @@ select jsonb_path_query_tz(
select jsonb_path_query(
'["2017-03-10 12:34:00", "2017-03-10 12:35:00", "2017-03-10 12:36:00", "2017-03-10 12:35:00+01", "2017-03-10 13:35:00+01", "2017-03-10 12:35:00-01", "2017-03-10", "2017-03-11"]',
'$[*].timestamp() ? (@ == "2017-03-10 12:35:00".timestamp())');
- jsonb_path_query
------------------------
- "2017-03-10T12:35:00"
- "2017-03-10T12:35:00"
-(2 rows)
-
+ERROR: cannot convert value from timestamptz to timestamp without time zone usage
+HINT: Use *_tz() function for time zone support.
select jsonb_path_query(
'["2017-03-10 12:34:00", "2017-03-10 12:35:00", "2017-03-10 12:36:00", "2017-03-10 12:35:00+01", "2017-03-10 13:35:00+01", "2017-03-10 12:35:00-01", "2017-03-10", "2017-03-11"]',
'$[*].timestamp() ? (@ >= "2017-03-10 12:35:00".timestamp())');
- jsonb_path_query
------------------------
- "2017-03-10T12:35:00"
- "2017-03-10T12:36:00"
- "2017-03-10T12:35:00"
- "2017-03-10T13:35:00"
- "2017-03-11T00:00:00"
-(5 rows)
-
+ERROR: cannot convert value from timestamptz to timestamp without time zone usage
+HINT: Use *_tz() function for time zone support.
select jsonb_path_query(
'["2017-03-10 12:34:00", "2017-03-10 12:35:00", "2017-03-10 12:36:00", "2017-03-10 12:35:00+01", "2017-03-10 13:35:00+01", "2017-03-10 12:35:00-01", "2017-03-10", "2017-03-11"]',
'$[*].timestamp() ? (@ < "2017-03-10 12:35:00".timestamp())');
- jsonb_path_query
------------------------
- "2017-03-10T12:34:00"
- "2017-03-10T11:35:00"
- "2017-03-10T00:00:00"
-(3 rows)
-
+ERROR: cannot convert value from timestamptz to timestamp without time zone usage
+HINT: Use *_tz() function for time zone support.
+select jsonb_path_query(
+ '["2017-03-10 12:34:00.123", "2017-03-10 12:35:00.123", "2017-03-10 12:36:00.1123", "2017-03-10 12:35:00.1123+01", "2017-03-10 13:35:00.123+01", "2017-03-10 12:35:00.1-01", "2017-03-10", "2017-03-11"]',
+ '$[*].timestamp(2) ? (@ >= "2017-03-10 12:35:00.123".timestamp(2))');
+ERROR: cannot convert value from timestamptz to timestamp without time zone usage
+HINT: Use *_tz() function for time zone support.
select jsonb_path_query_tz(
'["2017-03-10 12:34:00", "2017-03-10 12:35:00", "2017-03-10 12:36:00", "2017-03-10 12:35:00+01", "2017-03-10 13:35:00+01", "2017-03-10 12:35:00-01", "2017-03-10", "2017-03-11"]',
'$[*].timestamp() ? (@ == "2017-03-10 12:35:00".timestamp())');
@@ -3860,10 +3847,10 @@ select jsonb_path_query_tz(
"2017-03-10T00:00:00"
(3 rows)
-select jsonb_path_query(
+select jsonb_path_query_tz(
'["2017-03-10 12:34:00.123", "2017-03-10 12:35:00.123", "2017-03-10 12:36:00.1123", "2017-03-10 12:35:00.1123+01", "2017-03-10 13:35:00.123+01", "2017-03-10 12:35:00.1-01", "2017-03-10", "2017-03-11"]',
'$[*].timestamp(2) ? (@ >= "2017-03-10 12:35:00.123".timestamp(2))');
- jsonb_path_query
+ jsonb_path_query_tz
--------------------------
"2017-03-10T12:35:00.12"
"2017-03-10T12:36:00.11"
@@ -3957,36 +3944,23 @@ select jsonb_path_query_tz(
select jsonb_path_query(
'["2017-03-10 12:34:00+01", "2017-03-10 12:35:00+01", "2017-03-10 12:36:00+01", "2017-03-10 12:35:00+02", "2017-03-10 12:35:00-02", "2017-03-10 10:35:00", "2017-03-10 11:35:00", "2017-03-10 12:35:00", "2017-03-10", "2017-03-11"]',
'$[*].timestamp_tz() ? (@ == "2017-03-10 12:35:00 +1".timestamp_tz())');
- jsonb_path_query
------------------------------
- "2017-03-10T12:35:00+01:00"
- "2017-03-10T11:35:00+00:00"
-(2 rows)
-
+ERROR: cannot convert value from timestamp to timestamptz without time zone usage
+HINT: Use *_tz() function for time zone support.
select jsonb_path_query(
'["2017-03-10 12:34:00+01", "2017-03-10 12:35:00+01", "2017-03-10 12:36:00+01", "2017-03-10 12:35:00+02", "2017-03-10 12:35:00-02", "2017-03-10 10:35:00", "2017-03-10 11:35:00", "2017-03-10 12:35:00", "2017-03-10", "2017-03-11"]',
'$[*].timestamp_tz() ? (@ >= "2017-03-10 12:35:00 +1".timestamp_tz())');
- jsonb_path_query
------------------------------
- "2017-03-10T12:35:00+01:00"
- "2017-03-10T12:36:00+01:00"
- "2017-03-10T12:35:00-02:00"
- "2017-03-10T11:35:00+00:00"
- "2017-03-10T12:35:00+00:00"
- "2017-03-11T00:00:00+00:00"
-(6 rows)
-
+ERROR: cannot convert value from timestamp to timestamptz without time zone usage
+HINT: Use *_tz() function for time zone support.
select jsonb_path_query(
'["2017-03-10 12:34:00+01", "2017-03-10 12:35:00+01", "2017-03-10 12:36:00+01", "2017-03-10 12:35:00+02", "2017-03-10 12:35:00-02", "2017-03-10 10:35:00", "2017-03-10 11:35:00", "2017-03-10 12:35:00", "2017-03-10", "2017-03-11"]',
'$[*].timestamp_tz() ? (@ < "2017-03-10 12:35:00 +1".timestamp_tz())');
- jsonb_path_query
------------------------------
- "2017-03-10T12:34:00+01:00"
- "2017-03-10T12:35:00+02:00"
- "2017-03-10T10:35:00+00:00"
- "2017-03-10T00:00:00+00:00"
-(4 rows)
-
+ERROR: cannot convert value from timestamp to timestamptz without time zone usage
+HINT: Use *_tz() function for time zone support.
+select jsonb_path_query(
+ '["2017-03-10 12:34:00.123+01", "2017-03-10 12:35:00.123+01", "2017-03-10 12:36:00.1123+01", "2017-03-10 12:35:00.1123+02", "2017-03-10 12:35:00.123-02", "2017-03-10 10:35:00.123", "2017-03-10 11:35:00.1", "2017-03-10 12:35:00.123", "2017-03-10", "2017-03-11"]',
+ '$[*].timestamp_tz(2) ? (@ >= "2017-03-10 12:35:00.123 +1".timestamp_tz(2))');
+ERROR: cannot convert value from timestamp to timestamptz without time zone usage
+HINT: Use *_tz() function for time zone support.
select jsonb_path_query_tz(
'["2017-03-10 12:34:00+01", "2017-03-10 12:35:00+01", "2017-03-10 12:36:00+01", "2017-03-10 12:35:00+02", "2017-03-10 12:35:00-02", "2017-03-10 10:35:00", "2017-03-10 11:35:00", "2017-03-10 12:35:00", "2017-03-10", "2017-03-11"]',
'$[*].timestamp_tz() ? (@ == "2017-03-10 12:35:00 +1".timestamp_tz())');
@@ -4020,10 +3994,10 @@ select jsonb_path_query_tz(
"2017-03-10T00:00:00+00:00"
(4 rows)
-select jsonb_path_query(
+select jsonb_path_query_tz(
'["2017-03-10 12:34:00.123+01", "2017-03-10 12:35:00.123+01", "2017-03-10 12:36:00.1123+01", "2017-03-10 12:35:00.1123+02", "2017-03-10 12:35:00.123-02", "2017-03-10 10:35:00.123", "2017-03-10 11:35:00.1", "2017-03-10 12:35:00.123", "2017-03-10", "2017-03-11"]',
'$[*].timestamp_tz(2) ? (@ >= "2017-03-10 12:35:00.123 +1".timestamp_tz(2))');
- jsonb_path_query
+ jsonb_path_query_tz
--------------------------------
"2017-03-10T12:35:00.12+01:00"
"2017-03-10T12:36:00.11+01:00"
diff --git a/src/test/regress/sql/jsonb_jsonpath.sql b/src/test/regress/sql/jsonb_jsonpath.sql
index 6406a2e..cbd2db5 100644
--- a/src/test/regress/sql/jsonb_jsonpath.sql
+++ b/src/test/regress/sql/jsonb_jsonpath.sql
@@ -463,6 +463,7 @@ select jsonb_path_query('"12:34:56"', '$.date()');
select jsonb_path_query('"12:34:56 +05:30"', '$.date()');
select jsonb_path_query('"2023-08-15 12:34:56"', '$.date()');
select jsonb_path_query('"2023-08-15 12:34:56 +05:30"', '$.date()');
+select jsonb_path_query_tz('"2023-08-15 12:34:56 +05:30"', '$.date()'); -- should work
select jsonb_path_query('"2023-08-15"', '$.date(2)');
@@ -586,6 +587,7 @@ select jsonb_path_query('1234', '$.string()');
select jsonb_path_query('true', '$.string()');
select jsonb_path_query('1234', '$.string().type()');
select jsonb_path_query('"2023-08-15 12:34:56 +5:30"', '$.timestamp().string()');
+select jsonb_path_query_tz('"2023-08-15 12:34:56 +5:30"', '$.timestamp().string()'); -- should work
select jsonb_path_query_array('[1.23, "yes", false]', '$[*].string()');
select jsonb_path_query_array('[1.23, "yes", false]', '$[*].string().type()');
@@ -604,6 +606,7 @@ select jsonb_path_query('"12:34:56"', '$.time().type()');
select jsonb_path_query('"2023-08-15"', '$.time()');
select jsonb_path_query('"12:34:56 +05:30"', '$.time()');
+select jsonb_path_query_tz('"12:34:56 +05:30"', '$.time()'); -- should work
select jsonb_path_query('"2023-08-15 12:34:56"', '$.time()');
select jsonb_path_query('"12:34:56.789"', '$.time(-1)');
@@ -680,6 +683,7 @@ select jsonb_path_query('"2023-08-15 12:34:56 +05:30"', '$.timestamp_tz()');
select jsonb_path_query('"2023-08-15 12:34:56 +05:30"', '$.timestamp_tz().type()');
select jsonb_path_query('"2023-08-15"', '$.timestamp_tz()');
+select jsonb_path_query_tz('"2023-08-15"', '$.timestamp_tz()'); -- should work
select jsonb_path_query('"12:34:56"', '$.timestamp_tz()');
select jsonb_path_query('"12:34:56 +05:30"', '$.timestamp_tz()');
@@ -696,10 +700,14 @@ select jsonb_path_query('"2023-08-15 12:34:56.789012 +05:30"', '$.timestamp_tz(8
set time zone '+00';
select jsonb_path_query('"2023-08-15 12:34:56 +05:30"', '$.time()');
+select jsonb_path_query_tz('"2023-08-15 12:34:56 +05:30"', '$.time()'); -- should work
select jsonb_path_query('"2023-08-15 12:34:56 +05:30"', '$.time_tz()');
select jsonb_path_query('"12:34:56"', '$.time_tz()');
+select jsonb_path_query_tz('"12:34:56"', '$.time_tz()'); -- should work
select jsonb_path_query('"2023-08-15 12:34:56 +05:30"', '$.timestamp()');
+select jsonb_path_query_tz('"2023-08-15 12:34:56 +05:30"', '$.timestamp()'); -- should work
select jsonb_path_query('"2023-08-15 12:34:56"', '$.timestamp_tz()');
+select jsonb_path_query_tz('"2023-08-15 12:34:56"', '$.timestamp_tz()'); -- should work
select jsonb_path_query('"10-03-2017 12:34"', '$.datetime("dd-mm-yyyy HH24:MI")');
select jsonb_path_query('"10-03-2017 12:34"', '$.datetime("dd-mm-yyyy HH24:MI TZH")');
@@ -717,9 +725,12 @@ select jsonb_path_query('"12:34 -05:20"', '$.datetime("HH24:MI TZH:TZM")');
set time zone '+10';
select jsonb_path_query('"2023-08-15 12:34:56 +05:30"', '$.time()');
+select jsonb_path_query_tz('"2023-08-15 12:34:56 +05:30"', '$.time()'); -- should work
select jsonb_path_query('"2023-08-15 12:34:56 +05:30"', '$.time_tz()');
select jsonb_path_query('"2023-08-15 12:34:56 +05:30"', '$.timestamp()');
+select jsonb_path_query_tz('"2023-08-15 12:34:56 +05:30"', '$.timestamp()'); -- should work
select jsonb_path_query('"2023-08-15 12:34:56"', '$.timestamp_tz()');
+select jsonb_path_query_tz('"2023-08-15 12:34:56"', '$.timestamp_tz()'); -- should work
select jsonb_path_query('"2023-08-15 12:34:56 +05:30"', '$.timestamp_tz()');
select jsonb_path_query('"10-03-2017 12:34"', '$.datetime("dd-mm-yyyy HH24:MI")');
@@ -738,8 +749,10 @@ select jsonb_path_query('"12:34 -05:20"', '$.datetime("HH24:MI TZH:TZM")');
set time zone default;
select jsonb_path_query('"2023-08-15 12:34:56 +05:30"', '$.time()');
+select jsonb_path_query_tz('"2023-08-15 12:34:56 +05:30"', '$.time()'); -- should work
select jsonb_path_query('"2023-08-15 12:34:56 +05:30"', '$.time_tz()');
select jsonb_path_query('"2023-08-15 12:34:56 +05:30"', '$.timestamp()');
+select jsonb_path_query_tz('"2023-08-15 12:34:56 +05:30"', '$.timestamp()'); -- should work
select jsonb_path_query('"2023-08-15 12:34:56 +05:30"', '$.timestamp_tz()');
select jsonb_path_query('"2017-03-10"', '$.datetime().type()');
@@ -852,6 +865,9 @@ select jsonb_path_query(
select jsonb_path_query(
'["12:34:00", "12:35:00", "12:36:00", "12:35:00+00", "12:35:00+01", "13:35:00+01", "2017-03-10 12:35:00", "2017-03-10 12:35:00+01"]',
'$[*].time() ? (@ < "12:35:00".time())');
+select jsonb_path_query(
+ '["12:34:00.123", "12:35:00.123", "12:36:00.1123", "12:35:00.1123+00", "12:35:00.123+01", "13:35:00.123+01", "2017-03-10 12:35:00.1", "2017-03-10 12:35:00.123+01"]',
+ '$[*].time(2) ? (@ >= "12:35:00.123".time(2))');
select jsonb_path_query_tz(
'["12:34:00", "12:35:00", "12:36:00", "12:35:00+00", "12:35:00+01", "13:35:00+01", "2017-03-10 12:35:00", "2017-03-10 12:35:00+01"]',
'$[*].time() ? (@ == "12:35:00".time())');
@@ -861,7 +877,7 @@ select jsonb_path_query_tz(
select jsonb_path_query_tz(
'["12:34:00", "12:35:00", "12:36:00", "12:35:00+00", "12:35:00+01", "13:35:00+01", "2017-03-10 12:35:00", "2017-03-10 12:35:00+01"]',
'$[*].time() ? (@ < "12:35:00".time())');
-select jsonb_path_query(
+select jsonb_path_query_tz(
'["12:34:00.123", "12:35:00.123", "12:36:00.1123", "12:35:00.1123+00", "12:35:00.123+01", "13:35:00.123+01", "2017-03-10 12:35:00.1", "2017-03-10 12:35:00.123+01"]',
'$[*].time(2) ? (@ >= "12:35:00.123".time(2))');
@@ -904,6 +920,9 @@ select jsonb_path_query(
select jsonb_path_query(
'["12:34:00+01", "12:35:00+01", "12:36:00+01", "12:35:00+02", "12:35:00-02", "10:35:00", "11:35:00", "12:35:00", "2017-03-10 12:35:00 +1"]',
'$[*].time_tz() ? (@ < "12:35:00 +1".time_tz())');
+select jsonb_path_query(
+ '["12:34:00.123+01", "12:35:00.123+01", "12:36:00.1123+01", "12:35:00.1123+02", "12:35:00.123-02", "10:35:00.123", "11:35:00.1", "12:35:00.123", "2017-03-10 12:35:00.123 +1"]',
+ '$[*].time_tz(2) ? (@ >= "12:35:00.123 +1".time_tz(2))');
select jsonb_path_query_tz(
'["12:34:00+01", "12:35:00+01", "12:36:00+01", "12:35:00+02", "12:35:00-02", "10:35:00", "11:35:00", "12:35:00", "2017-03-10 12:35:00 +1"]',
'$[*].time_tz() ? (@ == "12:35:00 +1".time_tz())');
@@ -913,7 +932,7 @@ select jsonb_path_query_tz(
select jsonb_path_query_tz(
'["12:34:00+01", "12:35:00+01", "12:36:00+01", "12:35:00+02", "12:35:00-02", "10:35:00", "11:35:00", "12:35:00", "2017-03-10 12:35:00 +1"]',
'$[*].time_tz() ? (@ < "12:35:00 +1".time_tz())');
-select jsonb_path_query(
+select jsonb_path_query_tz(
'["12:34:00.123+01", "12:35:00.123+01", "12:36:00.1123+01", "12:35:00.1123+02", "12:35:00.123-02", "10:35:00.123", "11:35:00.1", "12:35:00.123", "2017-03-10 12:35:00.123 +1"]',
'$[*].time_tz(2) ? (@ >= "12:35:00.123 +1".time_tz(2))');
@@ -955,6 +974,9 @@ select jsonb_path_query(
select jsonb_path_query(
'["2017-03-10 12:34:00", "2017-03-10 12:35:00", "2017-03-10 12:36:00", "2017-03-10 12:35:00+01", "2017-03-10 13:35:00+01", "2017-03-10 12:35:00-01", "2017-03-10", "2017-03-11"]',
'$[*].timestamp() ? (@ < "2017-03-10 12:35:00".timestamp())');
+select jsonb_path_query(
+ '["2017-03-10 12:34:00.123", "2017-03-10 12:35:00.123", "2017-03-10 12:36:00.1123", "2017-03-10 12:35:00.1123+01", "2017-03-10 13:35:00.123+01", "2017-03-10 12:35:00.1-01", "2017-03-10", "2017-03-11"]',
+ '$[*].timestamp(2) ? (@ >= "2017-03-10 12:35:00.123".timestamp(2))');
select jsonb_path_query_tz(
'["2017-03-10 12:34:00", "2017-03-10 12:35:00", "2017-03-10 12:36:00", "2017-03-10 12:35:00+01", "2017-03-10 13:35:00+01", "2017-03-10 12:35:00-01", "2017-03-10", "2017-03-11"]',
'$[*].timestamp() ? (@ == "2017-03-10 12:35:00".timestamp())');
@@ -964,7 +986,7 @@ select jsonb_path_query_tz(
select jsonb_path_query_tz(
'["2017-03-10 12:34:00", "2017-03-10 12:35:00", "2017-03-10 12:36:00", "2017-03-10 12:35:00+01", "2017-03-10 13:35:00+01", "2017-03-10 12:35:00-01", "2017-03-10", "2017-03-11"]',
'$[*].timestamp() ? (@ < "2017-03-10 12:35:00".timestamp())');
-select jsonb_path_query(
+select jsonb_path_query_tz(
'["2017-03-10 12:34:00.123", "2017-03-10 12:35:00.123", "2017-03-10 12:36:00.1123", "2017-03-10 12:35:00.1123+01", "2017-03-10 13:35:00.123+01", "2017-03-10 12:35:00.1-01", "2017-03-10", "2017-03-11"]',
'$[*].timestamp(2) ? (@ >= "2017-03-10 12:35:00.123".timestamp(2))');
@@ -1006,6 +1028,9 @@ select jsonb_path_query(
select jsonb_path_query(
'["2017-03-10 12:34:00+01", "2017-03-10 12:35:00+01", "2017-03-10 12:36:00+01", "2017-03-10 12:35:00+02", "2017-03-10 12:35:00-02", "2017-03-10 10:35:00", "2017-03-10 11:35:00", "2017-03-10 12:35:00", "2017-03-10", "2017-03-11"]',
'$[*].timestamp_tz() ? (@ < "2017-03-10 12:35:00 +1".timestamp_tz())');
+select jsonb_path_query(
+ '["2017-03-10 12:34:00.123+01", "2017-03-10 12:35:00.123+01", "2017-03-10 12:36:00.1123+01", "2017-03-10 12:35:00.1123+02", "2017-03-10 12:35:00.123-02", "2017-03-10 10:35:00.123", "2017-03-10 11:35:00.1", "2017-03-10 12:35:00.123", "2017-03-10", "2017-03-11"]',
+ '$[*].timestamp_tz(2) ? (@ >= "2017-03-10 12:35:00.123 +1".timestamp_tz(2))');
select jsonb_path_query_tz(
'["2017-03-10 12:34:00+01", "2017-03-10 12:35:00+01", "2017-03-10 12:36:00+01", "2017-03-10 12:35:00+02", "2017-03-10 12:35:00-02", "2017-03-10 10:35:00", "2017-03-10 11:35:00", "2017-03-10 12:35:00", "2017-03-10", "2017-03-11"]',
'$[*].timestamp_tz() ? (@ == "2017-03-10 12:35:00 +1".timestamp_tz())');
@@ -1015,7 +1040,7 @@ select jsonb_path_query_tz(
select jsonb_path_query_tz(
'["2017-03-10 12:34:00+01", "2017-03-10 12:35:00+01", "2017-03-10 12:36:00+01", "2017-03-10 12:35:00+02", "2017-03-10 12:35:00-02", "2017-03-10 10:35:00", "2017-03-10 11:35:00", "2017-03-10 12:35:00", "2017-03-10", "2017-03-11"]',
'$[*].timestamp_tz() ? (@ < "2017-03-10 12:35:00 +1".timestamp_tz())');
-select jsonb_path_query(
+select jsonb_path_query_tz(
'["2017-03-10 12:34:00.123+01", "2017-03-10 12:35:00.123+01", "2017-03-10 12:36:00.1123+01", "2017-03-10 12:35:00.1123+02", "2017-03-10 12:35:00.123-02", "2017-03-10 10:35:00.123", "2017-03-10 11:35:00.1", "2017-03-10 12:35:00.123", "2017-03-10", "2017-03-11"]',
'$[*].timestamp_tz(2) ? (@ >= "2017-03-10 12:35:00.123 +1".timestamp_tz(2))');
Re: recently added jsonpath method change jsonb_path_query, jsonb_path_query_first immutability
On Wed, Feb 7, 2024 at 7:36 PM Jeevan Chalke
<jeevan.chalke@enterprisedb.com> wrote:
Added checkTimezoneIsUsedForCast() check where ever we are casting timezoned to non-timezoned types and vice-versa.
https://www.postgresql.org/docs/devel/functions-json.html
above Table 9.51. jsonpath Filter Expression Elements, the Note
section, do we also need to rephrase it?
Re: recently added jsonpath method change jsonb_path_query, jsonb_path_query_first immutability
On Wed, Feb 7, 2024 at 9:13 PM jian he <jian.universality@gmail.com> wrote:
On Wed, Feb 7, 2024 at 7:36 PM Jeevan Chalke
<jeevan.chalke@enterprisedb.com> wrote:Added checkTimezoneIsUsedForCast() check where ever we are casting
timezoned to non-timezoned types and vice-versa.
https://www.postgresql.org/docs/devel/functions-json.html
above Table 9.51. jsonpath Filter Expression Elements, the Note
section, do we also need to rephrase it?
OK. Added a line for the same.
Thanks
--
Jeevan Chalke
*Principal, ManagerProduct Development*
edbpostgres.com
Attachments:
v2-preserve-immutability.patchapplication/octet-stream; name=v2-preserve-immutability.patchDownload
commit a23dc60f8efea0406724e2c27c5c4c77ff6e3aff
Author: Jeevan Chalke <jeevan.chalke@enterprisedb.com>
Date: Wed Feb 7 17:01:25 2024 +0530
Preserve immutability in the date-time methods involving casting.
Timezones themselves are not immutable and could be updated. In
commit 66ea94e8e606529bb334515f388c62314956739e, we have introduced
a few methods which does the casting from one time to another and
may involve the current timezone. To preserve the immutability,
disallow them from being called from non-TZ aware function.
Jeevan Chalke, per a report from Jian He.
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 6788ba8..37ae2d1 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -18240,7 +18240,11 @@ ERROR: jsonpath member accessor can only be applied to an object
<type>timestamptz</type>, and <type>time</type> to <type>timetz</type>.
However, all but the first of these conversions depend on the current
<xref linkend="guc-timezone"/> setting, and thus can only be performed
- within timezone-aware <type>jsonpath</type> functions.
+ within timezone-aware <type>jsonpath</type> functions. Similarly, other
+ date/time-related methods that convert string to the date/time types
+ also do the casting and may involve the current
+ <xref linkend="guc-timezone"/>. To preserve the immutability, those can
+ only be performed within timezone-aware <type>jsonpath</type> functions.
</para>
</note>
diff --git a/src/backend/utils/adt/jsonpath_exec.c b/src/backend/utils/adt/jsonpath_exec.c
index 573b6ce..8372863 100644
--- a/src/backend/utils/adt/jsonpath_exec.c
+++ b/src/backend/utils/adt/jsonpath_exec.c
@@ -268,6 +268,8 @@ static JsonbValue *getScalar(JsonbValue *scalar, enum jbvType type);
static JsonbValue *wrapItemsInArray(const JsonValueList *items);
static int compareDatetime(Datum val1, Oid typid1, Datum val2, Oid typid2,
bool useTz, bool *cast_error);
+static void checkTimezoneIsUsedForCast(bool useTz, const char *type1,
+ const char *type2);
/****************** User interface to JsonPath executor ********************/
@@ -2409,6 +2411,8 @@ executeDateTimeMethod(JsonPathExecContext *cxt, JsonPathItem *jsp,
value);
break;
case TIMESTAMPTZOID:
+ checkTimezoneIsUsedForCast(cxt->useTz,
+ "timestamptz", "date");
value = DirectFunctionCall1(timestamptz_date,
value);
break;
@@ -2433,6 +2437,8 @@ executeDateTimeMethod(JsonPathExecContext *cxt, JsonPathItem *jsp,
case TIMEOID: /* Nothing to do for TIME */
break;
case TIMETZOID:
+ checkTimezoneIsUsedForCast(cxt->useTz,
+ "timetz", "time");
value = DirectFunctionCall1(timetz_time,
value);
break;
@@ -2441,6 +2447,8 @@ executeDateTimeMethod(JsonPathExecContext *cxt, JsonPathItem *jsp,
value);
break;
case TIMESTAMPTZOID:
+ checkTimezoneIsUsedForCast(cxt->useTz,
+ "timestamptz", "time");
value = DirectFunctionCall1(timestamptz_time,
value);
break;
@@ -2480,6 +2488,8 @@ executeDateTimeMethod(JsonPathExecContext *cxt, JsonPathItem *jsp,
text_to_cstring(datetime)))));
break;
case TIMEOID:
+ checkTimezoneIsUsedForCast(cxt->useTz,
+ "time", "timetz");
value = DirectFunctionCall1(time_timetz,
value);
break;
@@ -2531,6 +2541,8 @@ executeDateTimeMethod(JsonPathExecContext *cxt, JsonPathItem *jsp,
case TIMESTAMPOID: /* Nothing to do for TIMESTAMP */
break;
case TIMESTAMPTZOID:
+ checkTimezoneIsUsedForCast(cxt->useTz,
+ "timestamptz", "timestamp");
value = DirectFunctionCall1(timestamptz_timestamp,
value);
break;
@@ -2570,6 +2582,8 @@ executeDateTimeMethod(JsonPathExecContext *cxt, JsonPathItem *jsp,
switch (typid)
{
case DATEOID:
+ checkTimezoneIsUsedForCast(cxt->useTz,
+ "date", "timestamptz");
value = DirectFunctionCall1(date_timestamptz,
value);
break;
@@ -2581,6 +2595,8 @@ executeDateTimeMethod(JsonPathExecContext *cxt, JsonPathItem *jsp,
text_to_cstring(datetime)))));
break;
case TIMESTAMPOID:
+ checkTimezoneIsUsedForCast(cxt->useTz,
+ "timestamp", "timestamptz");
value = DirectFunctionCall1(timestamp_timestamptz,
value);
break;
diff --git a/src/test/regress/expected/jsonb_jsonpath.out b/src/test/regress/expected/jsonb_jsonpath.out
index eea2af3..414c296 100644
--- a/src/test/regress/expected/jsonb_jsonpath.out
+++ b/src/test/regress/expected/jsonb_jsonpath.out
@@ -2088,8 +2088,11 @@ select jsonb_path_query('"2023-08-15 12:34:56"', '$.date()');
(1 row)
select jsonb_path_query('"2023-08-15 12:34:56 +05:30"', '$.date()');
- jsonb_path_query
-------------------
+ERROR: cannot convert value from timestamptz to date without time zone usage
+HINT: Use *_tz() function for time zone support.
+select jsonb_path_query_tz('"2023-08-15 12:34:56 +05:30"', '$.date()'); -- should work
+ jsonb_path_query_tz
+---------------------
"2023-08-15"
(1 row)
@@ -2574,7 +2577,10 @@ select jsonb_path_query('1234', '$.string().type()');
(1 row)
select jsonb_path_query('"2023-08-15 12:34:56 +5:30"', '$.timestamp().string()');
- jsonb_path_query
+ERROR: cannot convert value from timestamptz to timestamp without time zone usage
+HINT: Use *_tz() function for time zone support.
+select jsonb_path_query_tz('"2023-08-15 12:34:56 +5:30"', '$.timestamp().string()'); -- should work
+ jsonb_path_query_tz
----------------------------
"Tue Aug 15 00:04:56 2023"
(1 row)
@@ -2630,8 +2636,11 @@ select jsonb_path_query('"12:34:56"', '$.time().type()');
select jsonb_path_query('"2023-08-15"', '$.time()');
ERROR: time format is not recognized: "2023-08-15"
select jsonb_path_query('"12:34:56 +05:30"', '$.time()');
- jsonb_path_query
-------------------
+ERROR: cannot convert value from timetz to time without time zone usage
+HINT: Use *_tz() function for time zone support.
+select jsonb_path_query_tz('"12:34:56 +05:30"', '$.time()'); -- should work
+ jsonb_path_query_tz
+---------------------
"12:34:56"
(1 row)
@@ -2890,7 +2899,10 @@ select jsonb_path_query('"2023-08-15 12:34:56 +05:30"', '$.timestamp_tz().type()
(1 row)
select jsonb_path_query('"2023-08-15"', '$.timestamp_tz()');
- jsonb_path_query
+ERROR: cannot convert value from date to timestamptz without time zone usage
+HINT: Use *_tz() function for time zone support.
+select jsonb_path_query_tz('"2023-08-15"', '$.timestamp_tz()'); -- should work
+ jsonb_path_query_tz
-----------------------------
"2023-08-15T07:00:00+00:00"
(1 row)
@@ -2943,8 +2955,11 @@ WARNING: TIMESTAMP(8) WITH TIME ZONE precision reduced to maximum allowed, 6
set time zone '+00';
select jsonb_path_query('"2023-08-15 12:34:56 +05:30"', '$.time()');
- jsonb_path_query
-------------------
+ERROR: cannot convert value from timestamptz to time without time zone usage
+HINT: Use *_tz() function for time zone support.
+select jsonb_path_query_tz('"2023-08-15 12:34:56 +05:30"', '$.time()'); -- should work
+ jsonb_path_query_tz
+---------------------
"07:04:56"
(1 row)
@@ -2955,19 +2970,28 @@ select jsonb_path_query('"2023-08-15 12:34:56 +05:30"', '$.time_tz()');
(1 row)
select jsonb_path_query('"12:34:56"', '$.time_tz()');
- jsonb_path_query
-------------------
+ERROR: cannot convert value from time to timetz without time zone usage
+HINT: Use *_tz() function for time zone support.
+select jsonb_path_query_tz('"12:34:56"', '$.time_tz()'); -- should work
+ jsonb_path_query_tz
+---------------------
"12:34:56+00:00"
(1 row)
select jsonb_path_query('"2023-08-15 12:34:56 +05:30"', '$.timestamp()');
- jsonb_path_query
+ERROR: cannot convert value from timestamptz to timestamp without time zone usage
+HINT: Use *_tz() function for time zone support.
+select jsonb_path_query_tz('"2023-08-15 12:34:56 +05:30"', '$.timestamp()'); -- should work
+ jsonb_path_query_tz
-----------------------
"2023-08-15T07:04:56"
(1 row)
select jsonb_path_query('"2023-08-15 12:34:56"', '$.timestamp_tz()');
- jsonb_path_query
+ERROR: cannot convert value from timestamp to timestamptz without time zone usage
+HINT: Use *_tz() function for time zone support.
+select jsonb_path_query_tz('"2023-08-15 12:34:56"', '$.timestamp_tz()'); -- should work
+ jsonb_path_query_tz
-----------------------------
"2023-08-15T12:34:56+00:00"
(1 row)
@@ -3038,8 +3062,11 @@ select jsonb_path_query('"12:34 -05:20"', '$.datetime("HH24:MI TZH:TZM")');
set time zone '+10';
select jsonb_path_query('"2023-08-15 12:34:56 +05:30"', '$.time()');
- jsonb_path_query
-------------------
+ERROR: cannot convert value from timestamptz to time without time zone usage
+HINT: Use *_tz() function for time zone support.
+select jsonb_path_query_tz('"2023-08-15 12:34:56 +05:30"', '$.time()'); -- should work
+ jsonb_path_query_tz
+---------------------
"17:04:56"
(1 row)
@@ -3050,13 +3077,19 @@ select jsonb_path_query('"2023-08-15 12:34:56 +05:30"', '$.time_tz()');
(1 row)
select jsonb_path_query('"2023-08-15 12:34:56 +05:30"', '$.timestamp()');
- jsonb_path_query
+ERROR: cannot convert value from timestamptz to timestamp without time zone usage
+HINT: Use *_tz() function for time zone support.
+select jsonb_path_query_tz('"2023-08-15 12:34:56 +05:30"', '$.timestamp()'); -- should work
+ jsonb_path_query_tz
-----------------------
"2023-08-15T17:04:56"
(1 row)
select jsonb_path_query('"2023-08-15 12:34:56"', '$.timestamp_tz()');
- jsonb_path_query
+ERROR: cannot convert value from timestamp to timestamptz without time zone usage
+HINT: Use *_tz() function for time zone support.
+select jsonb_path_query_tz('"2023-08-15 12:34:56"', '$.timestamp_tz()'); -- should work
+ jsonb_path_query_tz
-----------------------------
"2023-08-15T02:34:56+00:00"
(1 row)
@@ -3133,8 +3166,11 @@ select jsonb_path_query('"12:34 -05:20"', '$.datetime("HH24:MI TZH:TZM")');
set time zone default;
select jsonb_path_query('"2023-08-15 12:34:56 +05:30"', '$.time()');
- jsonb_path_query
-------------------
+ERROR: cannot convert value from timestamptz to time without time zone usage
+HINT: Use *_tz() function for time zone support.
+select jsonb_path_query_tz('"2023-08-15 12:34:56 +05:30"', '$.time()'); -- should work
+ jsonb_path_query_tz
+---------------------
"00:04:56"
(1 row)
@@ -3145,7 +3181,10 @@ select jsonb_path_query('"2023-08-15 12:34:56 +05:30"', '$.time_tz()');
(1 row)
select jsonb_path_query('"2023-08-15 12:34:56 +05:30"', '$.timestamp()');
- jsonb_path_query
+ERROR: cannot convert value from timestamptz to timestamp without time zone usage
+HINT: Use *_tz() function for time zone support.
+select jsonb_path_query_tz('"2023-08-15 12:34:56 +05:30"', '$.timestamp()'); -- should work
+ jsonb_path_query_tz
-----------------------
"2023-08-15T00:04:56"
(1 row)
@@ -3358,35 +3397,18 @@ select jsonb_path_query_tz(
select jsonb_path_query(
'["2017-03-10", "2017-03-11", "2017-03-09", "2017-03-10 00:00:00", "2017-03-10 12:34:56", "2017-03-10 01:02:03+04", "2017-03-10 03:00:00+03"]',
'$[*].date() ? (@ == "2017-03-10".date())');
- jsonb_path_query
-------------------
- "2017-03-10"
- "2017-03-10"
- "2017-03-10"
- "2017-03-10"
-(4 rows)
-
+ERROR: cannot convert value from timestamptz to date without time zone usage
+HINT: Use *_tz() function for time zone support.
select jsonb_path_query(
'["2017-03-10", "2017-03-11", "2017-03-09", "2017-03-10 00:00:00", "2017-03-10 12:34:56", "2017-03-10 01:02:03+04", "2017-03-10 03:00:00+03"]',
'$[*].date() ? (@ >= "2017-03-10".date())');
- jsonb_path_query
-------------------
- "2017-03-10"
- "2017-03-11"
- "2017-03-10"
- "2017-03-10"
- "2017-03-10"
-(5 rows)
-
+ERROR: cannot convert value from timestamptz to date without time zone usage
+HINT: Use *_tz() function for time zone support.
select jsonb_path_query(
'["2017-03-10", "2017-03-11", "2017-03-09", "2017-03-10 00:00:00", "2017-03-10 12:34:56", "2017-03-10 01:02:03+04", "2017-03-10 03:00:00+03"]',
'$[*].date() ? (@ < "2017-03-10".date())');
- jsonb_path_query
-------------------
- "2017-03-09"
- "2017-03-09"
-(2 rows)
-
+ERROR: cannot convert value from timestamptz to date without time zone usage
+HINT: Use *_tz() function for time zone support.
select jsonb_path_query_tz(
'["2017-03-10", "2017-03-11", "2017-03-09", "2017-03-10 00:00:00", "2017-03-10 12:34:56", "2017-03-10 01:02:03+04", "2017-03-10 03:00:00+03"]',
'$[*].date() ? (@ == "2017-03-10".date())');
@@ -3496,36 +3518,23 @@ select jsonb_path_query_tz(
select jsonb_path_query(
'["12:34:00", "12:35:00", "12:36:00", "12:35:00+00", "12:35:00+01", "13:35:00+01", "2017-03-10 12:35:00", "2017-03-10 12:35:00+01"]',
'$[*].time() ? (@ == "12:35:00".time())');
- jsonb_path_query
-------------------
- "12:35:00"
- "12:35:00"
- "12:35:00"
- "12:35:00"
-(4 rows)
-
+ERROR: cannot convert value from timetz to time without time zone usage
+HINT: Use *_tz() function for time zone support.
select jsonb_path_query(
'["12:34:00", "12:35:00", "12:36:00", "12:35:00+00", "12:35:00+01", "13:35:00+01", "2017-03-10 12:35:00", "2017-03-10 12:35:00+01"]',
'$[*].time() ? (@ >= "12:35:00".time())');
- jsonb_path_query
-------------------
- "12:35:00"
- "12:36:00"
- "12:35:00"
- "12:35:00"
- "13:35:00"
- "12:35:00"
-(6 rows)
-
+ERROR: cannot convert value from timetz to time without time zone usage
+HINT: Use *_tz() function for time zone support.
select jsonb_path_query(
'["12:34:00", "12:35:00", "12:36:00", "12:35:00+00", "12:35:00+01", "13:35:00+01", "2017-03-10 12:35:00", "2017-03-10 12:35:00+01"]',
'$[*].time() ? (@ < "12:35:00".time())');
- jsonb_path_query
-------------------
- "12:34:00"
- "11:35:00"
-(2 rows)
-
+ERROR: cannot convert value from timetz to time without time zone usage
+HINT: Use *_tz() function for time zone support.
+select jsonb_path_query(
+ '["12:34:00.123", "12:35:00.123", "12:36:00.1123", "12:35:00.1123+00", "12:35:00.123+01", "13:35:00.123+01", "2017-03-10 12:35:00.1", "2017-03-10 12:35:00.123+01"]',
+ '$[*].time(2) ? (@ >= "12:35:00.123".time(2))');
+ERROR: cannot convert value from timetz to time without time zone usage
+HINT: Use *_tz() function for time zone support.
select jsonb_path_query_tz(
'["12:34:00", "12:35:00", "12:36:00", "12:35:00+00", "12:35:00+01", "13:35:00+01", "2017-03-10 12:35:00", "2017-03-10 12:35:00+01"]',
'$[*].time() ? (@ == "12:35:00".time())');
@@ -3559,11 +3568,11 @@ select jsonb_path_query_tz(
"11:35:00"
(2 rows)
-select jsonb_path_query(
+select jsonb_path_query_tz(
'["12:34:00.123", "12:35:00.123", "12:36:00.1123", "12:35:00.1123+00", "12:35:00.123+01", "13:35:00.123+01", "2017-03-10 12:35:00.1", "2017-03-10 12:35:00.123+01"]',
'$[*].time(2) ? (@ >= "12:35:00.123".time(2))');
- jsonb_path_query
-------------------
+ jsonb_path_query_tz
+---------------------
"12:35:00.12"
"12:36:00.11"
"12:35:00.12"
@@ -3649,34 +3658,23 @@ select jsonb_path_query_tz(
select jsonb_path_query(
'["12:34:00+01", "12:35:00+01", "12:36:00+01", "12:35:00+02", "12:35:00-02", "10:35:00", "11:35:00", "12:35:00", "2017-03-10 12:35:00 +1"]',
'$[*].time_tz() ? (@ == "12:35:00 +1".time_tz())');
- jsonb_path_query
-------------------
- "12:35:00+01:00"
-(1 row)
-
+ERROR: cannot convert value from time to timetz without time zone usage
+HINT: Use *_tz() function for time zone support.
select jsonb_path_query(
'["12:34:00+01", "12:35:00+01", "12:36:00+01", "12:35:00+02", "12:35:00-02", "10:35:00", "11:35:00", "12:35:00", "2017-03-10 12:35:00 +1"]',
'$[*].time_tz() ? (@ >= "12:35:00 +1".time_tz())');
- jsonb_path_query
-------------------
- "12:35:00+01:00"
- "12:36:00+01:00"
- "12:35:00-02:00"
- "11:35:00+00:00"
- "12:35:00+00:00"
- "11:35:00+00:00"
-(6 rows)
-
+ERROR: cannot convert value from time to timetz without time zone usage
+HINT: Use *_tz() function for time zone support.
select jsonb_path_query(
'["12:34:00+01", "12:35:00+01", "12:36:00+01", "12:35:00+02", "12:35:00-02", "10:35:00", "11:35:00", "12:35:00", "2017-03-10 12:35:00 +1"]',
'$[*].time_tz() ? (@ < "12:35:00 +1".time_tz())');
- jsonb_path_query
-------------------
- "12:34:00+01:00"
- "12:35:00+02:00"
- "10:35:00+00:00"
-(3 rows)
-
+ERROR: cannot convert value from time to timetz without time zone usage
+HINT: Use *_tz() function for time zone support.
+select jsonb_path_query(
+ '["12:34:00.123+01", "12:35:00.123+01", "12:36:00.1123+01", "12:35:00.1123+02", "12:35:00.123-02", "10:35:00.123", "11:35:00.1", "12:35:00.123", "2017-03-10 12:35:00.123 +1"]',
+ '$[*].time_tz(2) ? (@ >= "12:35:00.123 +1".time_tz(2))');
+ERROR: cannot convert value from time to timetz without time zone usage
+HINT: Use *_tz() function for time zone support.
select jsonb_path_query_tz(
'["12:34:00+01", "12:35:00+01", "12:36:00+01", "12:35:00+02", "12:35:00-02", "10:35:00", "11:35:00", "12:35:00", "2017-03-10 12:35:00 +1"]',
'$[*].time_tz() ? (@ == "12:35:00 +1".time_tz())');
@@ -3708,10 +3706,10 @@ select jsonb_path_query_tz(
"10:35:00+00:00"
(3 rows)
-select jsonb_path_query(
+select jsonb_path_query_tz(
'["12:34:00.123+01", "12:35:00.123+01", "12:36:00.1123+01", "12:35:00.1123+02", "12:35:00.123-02", "10:35:00.123", "11:35:00.1", "12:35:00.123", "2017-03-10 12:35:00.123 +1"]',
'$[*].time_tz(2) ? (@ >= "12:35:00.123 +1".time_tz(2))');
- jsonb_path_query
+ jsonb_path_query_tz
---------------------
"12:35:00.12+01:00"
"12:36:00.11+01:00"
@@ -3801,34 +3799,23 @@ select jsonb_path_query_tz(
select jsonb_path_query(
'["2017-03-10 12:34:00", "2017-03-10 12:35:00", "2017-03-10 12:36:00", "2017-03-10 12:35:00+01", "2017-03-10 13:35:00+01", "2017-03-10 12:35:00-01", "2017-03-10", "2017-03-11"]',
'$[*].timestamp() ? (@ == "2017-03-10 12:35:00".timestamp())');
- jsonb_path_query
------------------------
- "2017-03-10T12:35:00"
- "2017-03-10T12:35:00"
-(2 rows)
-
+ERROR: cannot convert value from timestamptz to timestamp without time zone usage
+HINT: Use *_tz() function for time zone support.
select jsonb_path_query(
'["2017-03-10 12:34:00", "2017-03-10 12:35:00", "2017-03-10 12:36:00", "2017-03-10 12:35:00+01", "2017-03-10 13:35:00+01", "2017-03-10 12:35:00-01", "2017-03-10", "2017-03-11"]',
'$[*].timestamp() ? (@ >= "2017-03-10 12:35:00".timestamp())');
- jsonb_path_query
------------------------
- "2017-03-10T12:35:00"
- "2017-03-10T12:36:00"
- "2017-03-10T12:35:00"
- "2017-03-10T13:35:00"
- "2017-03-11T00:00:00"
-(5 rows)
-
+ERROR: cannot convert value from timestamptz to timestamp without time zone usage
+HINT: Use *_tz() function for time zone support.
select jsonb_path_query(
'["2017-03-10 12:34:00", "2017-03-10 12:35:00", "2017-03-10 12:36:00", "2017-03-10 12:35:00+01", "2017-03-10 13:35:00+01", "2017-03-10 12:35:00-01", "2017-03-10", "2017-03-11"]',
'$[*].timestamp() ? (@ < "2017-03-10 12:35:00".timestamp())');
- jsonb_path_query
------------------------
- "2017-03-10T12:34:00"
- "2017-03-10T11:35:00"
- "2017-03-10T00:00:00"
-(3 rows)
-
+ERROR: cannot convert value from timestamptz to timestamp without time zone usage
+HINT: Use *_tz() function for time zone support.
+select jsonb_path_query(
+ '["2017-03-10 12:34:00.123", "2017-03-10 12:35:00.123", "2017-03-10 12:36:00.1123", "2017-03-10 12:35:00.1123+01", "2017-03-10 13:35:00.123+01", "2017-03-10 12:35:00.1-01", "2017-03-10", "2017-03-11"]',
+ '$[*].timestamp(2) ? (@ >= "2017-03-10 12:35:00.123".timestamp(2))');
+ERROR: cannot convert value from timestamptz to timestamp without time zone usage
+HINT: Use *_tz() function for time zone support.
select jsonb_path_query_tz(
'["2017-03-10 12:34:00", "2017-03-10 12:35:00", "2017-03-10 12:36:00", "2017-03-10 12:35:00+01", "2017-03-10 13:35:00+01", "2017-03-10 12:35:00-01", "2017-03-10", "2017-03-11"]',
'$[*].timestamp() ? (@ == "2017-03-10 12:35:00".timestamp())');
@@ -3860,10 +3847,10 @@ select jsonb_path_query_tz(
"2017-03-10T00:00:00"
(3 rows)
-select jsonb_path_query(
+select jsonb_path_query_tz(
'["2017-03-10 12:34:00.123", "2017-03-10 12:35:00.123", "2017-03-10 12:36:00.1123", "2017-03-10 12:35:00.1123+01", "2017-03-10 13:35:00.123+01", "2017-03-10 12:35:00.1-01", "2017-03-10", "2017-03-11"]',
'$[*].timestamp(2) ? (@ >= "2017-03-10 12:35:00.123".timestamp(2))');
- jsonb_path_query
+ jsonb_path_query_tz
--------------------------
"2017-03-10T12:35:00.12"
"2017-03-10T12:36:00.11"
@@ -3957,36 +3944,23 @@ select jsonb_path_query_tz(
select jsonb_path_query(
'["2017-03-10 12:34:00+01", "2017-03-10 12:35:00+01", "2017-03-10 12:36:00+01", "2017-03-10 12:35:00+02", "2017-03-10 12:35:00-02", "2017-03-10 10:35:00", "2017-03-10 11:35:00", "2017-03-10 12:35:00", "2017-03-10", "2017-03-11"]',
'$[*].timestamp_tz() ? (@ == "2017-03-10 12:35:00 +1".timestamp_tz())');
- jsonb_path_query
------------------------------
- "2017-03-10T12:35:00+01:00"
- "2017-03-10T11:35:00+00:00"
-(2 rows)
-
+ERROR: cannot convert value from timestamp to timestamptz without time zone usage
+HINT: Use *_tz() function for time zone support.
select jsonb_path_query(
'["2017-03-10 12:34:00+01", "2017-03-10 12:35:00+01", "2017-03-10 12:36:00+01", "2017-03-10 12:35:00+02", "2017-03-10 12:35:00-02", "2017-03-10 10:35:00", "2017-03-10 11:35:00", "2017-03-10 12:35:00", "2017-03-10", "2017-03-11"]',
'$[*].timestamp_tz() ? (@ >= "2017-03-10 12:35:00 +1".timestamp_tz())');
- jsonb_path_query
------------------------------
- "2017-03-10T12:35:00+01:00"
- "2017-03-10T12:36:00+01:00"
- "2017-03-10T12:35:00-02:00"
- "2017-03-10T11:35:00+00:00"
- "2017-03-10T12:35:00+00:00"
- "2017-03-11T00:00:00+00:00"
-(6 rows)
-
+ERROR: cannot convert value from timestamp to timestamptz without time zone usage
+HINT: Use *_tz() function for time zone support.
select jsonb_path_query(
'["2017-03-10 12:34:00+01", "2017-03-10 12:35:00+01", "2017-03-10 12:36:00+01", "2017-03-10 12:35:00+02", "2017-03-10 12:35:00-02", "2017-03-10 10:35:00", "2017-03-10 11:35:00", "2017-03-10 12:35:00", "2017-03-10", "2017-03-11"]',
'$[*].timestamp_tz() ? (@ < "2017-03-10 12:35:00 +1".timestamp_tz())');
- jsonb_path_query
------------------------------
- "2017-03-10T12:34:00+01:00"
- "2017-03-10T12:35:00+02:00"
- "2017-03-10T10:35:00+00:00"
- "2017-03-10T00:00:00+00:00"
-(4 rows)
-
+ERROR: cannot convert value from timestamp to timestamptz without time zone usage
+HINT: Use *_tz() function for time zone support.
+select jsonb_path_query(
+ '["2017-03-10 12:34:00.123+01", "2017-03-10 12:35:00.123+01", "2017-03-10 12:36:00.1123+01", "2017-03-10 12:35:00.1123+02", "2017-03-10 12:35:00.123-02", "2017-03-10 10:35:00.123", "2017-03-10 11:35:00.1", "2017-03-10 12:35:00.123", "2017-03-10", "2017-03-11"]',
+ '$[*].timestamp_tz(2) ? (@ >= "2017-03-10 12:35:00.123 +1".timestamp_tz(2))');
+ERROR: cannot convert value from timestamp to timestamptz without time zone usage
+HINT: Use *_tz() function for time zone support.
select jsonb_path_query_tz(
'["2017-03-10 12:34:00+01", "2017-03-10 12:35:00+01", "2017-03-10 12:36:00+01", "2017-03-10 12:35:00+02", "2017-03-10 12:35:00-02", "2017-03-10 10:35:00", "2017-03-10 11:35:00", "2017-03-10 12:35:00", "2017-03-10", "2017-03-11"]',
'$[*].timestamp_tz() ? (@ == "2017-03-10 12:35:00 +1".timestamp_tz())');
@@ -4020,10 +3994,10 @@ select jsonb_path_query_tz(
"2017-03-10T00:00:00+00:00"
(4 rows)
-select jsonb_path_query(
+select jsonb_path_query_tz(
'["2017-03-10 12:34:00.123+01", "2017-03-10 12:35:00.123+01", "2017-03-10 12:36:00.1123+01", "2017-03-10 12:35:00.1123+02", "2017-03-10 12:35:00.123-02", "2017-03-10 10:35:00.123", "2017-03-10 11:35:00.1", "2017-03-10 12:35:00.123", "2017-03-10", "2017-03-11"]',
'$[*].timestamp_tz(2) ? (@ >= "2017-03-10 12:35:00.123 +1".timestamp_tz(2))');
- jsonb_path_query
+ jsonb_path_query_tz
--------------------------------
"2017-03-10T12:35:00.12+01:00"
"2017-03-10T12:36:00.11+01:00"
diff --git a/src/test/regress/sql/jsonb_jsonpath.sql b/src/test/regress/sql/jsonb_jsonpath.sql
index 6406a2e..cbd2db5 100644
--- a/src/test/regress/sql/jsonb_jsonpath.sql
+++ b/src/test/regress/sql/jsonb_jsonpath.sql
@@ -463,6 +463,7 @@ select jsonb_path_query('"12:34:56"', '$.date()');
select jsonb_path_query('"12:34:56 +05:30"', '$.date()');
select jsonb_path_query('"2023-08-15 12:34:56"', '$.date()');
select jsonb_path_query('"2023-08-15 12:34:56 +05:30"', '$.date()');
+select jsonb_path_query_tz('"2023-08-15 12:34:56 +05:30"', '$.date()'); -- should work
select jsonb_path_query('"2023-08-15"', '$.date(2)');
@@ -586,6 +587,7 @@ select jsonb_path_query('1234', '$.string()');
select jsonb_path_query('true', '$.string()');
select jsonb_path_query('1234', '$.string().type()');
select jsonb_path_query('"2023-08-15 12:34:56 +5:30"', '$.timestamp().string()');
+select jsonb_path_query_tz('"2023-08-15 12:34:56 +5:30"', '$.timestamp().string()'); -- should work
select jsonb_path_query_array('[1.23, "yes", false]', '$[*].string()');
select jsonb_path_query_array('[1.23, "yes", false]', '$[*].string().type()');
@@ -604,6 +606,7 @@ select jsonb_path_query('"12:34:56"', '$.time().type()');
select jsonb_path_query('"2023-08-15"', '$.time()');
select jsonb_path_query('"12:34:56 +05:30"', '$.time()');
+select jsonb_path_query_tz('"12:34:56 +05:30"', '$.time()'); -- should work
select jsonb_path_query('"2023-08-15 12:34:56"', '$.time()');
select jsonb_path_query('"12:34:56.789"', '$.time(-1)');
@@ -680,6 +683,7 @@ select jsonb_path_query('"2023-08-15 12:34:56 +05:30"', '$.timestamp_tz()');
select jsonb_path_query('"2023-08-15 12:34:56 +05:30"', '$.timestamp_tz().type()');
select jsonb_path_query('"2023-08-15"', '$.timestamp_tz()');
+select jsonb_path_query_tz('"2023-08-15"', '$.timestamp_tz()'); -- should work
select jsonb_path_query('"12:34:56"', '$.timestamp_tz()');
select jsonb_path_query('"12:34:56 +05:30"', '$.timestamp_tz()');
@@ -696,10 +700,14 @@ select jsonb_path_query('"2023-08-15 12:34:56.789012 +05:30"', '$.timestamp_tz(8
set time zone '+00';
select jsonb_path_query('"2023-08-15 12:34:56 +05:30"', '$.time()');
+select jsonb_path_query_tz('"2023-08-15 12:34:56 +05:30"', '$.time()'); -- should work
select jsonb_path_query('"2023-08-15 12:34:56 +05:30"', '$.time_tz()');
select jsonb_path_query('"12:34:56"', '$.time_tz()');
+select jsonb_path_query_tz('"12:34:56"', '$.time_tz()'); -- should work
select jsonb_path_query('"2023-08-15 12:34:56 +05:30"', '$.timestamp()');
+select jsonb_path_query_tz('"2023-08-15 12:34:56 +05:30"', '$.timestamp()'); -- should work
select jsonb_path_query('"2023-08-15 12:34:56"', '$.timestamp_tz()');
+select jsonb_path_query_tz('"2023-08-15 12:34:56"', '$.timestamp_tz()'); -- should work
select jsonb_path_query('"10-03-2017 12:34"', '$.datetime("dd-mm-yyyy HH24:MI")');
select jsonb_path_query('"10-03-2017 12:34"', '$.datetime("dd-mm-yyyy HH24:MI TZH")');
@@ -717,9 +725,12 @@ select jsonb_path_query('"12:34 -05:20"', '$.datetime("HH24:MI TZH:TZM")');
set time zone '+10';
select jsonb_path_query('"2023-08-15 12:34:56 +05:30"', '$.time()');
+select jsonb_path_query_tz('"2023-08-15 12:34:56 +05:30"', '$.time()'); -- should work
select jsonb_path_query('"2023-08-15 12:34:56 +05:30"', '$.time_tz()');
select jsonb_path_query('"2023-08-15 12:34:56 +05:30"', '$.timestamp()');
+select jsonb_path_query_tz('"2023-08-15 12:34:56 +05:30"', '$.timestamp()'); -- should work
select jsonb_path_query('"2023-08-15 12:34:56"', '$.timestamp_tz()');
+select jsonb_path_query_tz('"2023-08-15 12:34:56"', '$.timestamp_tz()'); -- should work
select jsonb_path_query('"2023-08-15 12:34:56 +05:30"', '$.timestamp_tz()');
select jsonb_path_query('"10-03-2017 12:34"', '$.datetime("dd-mm-yyyy HH24:MI")');
@@ -738,8 +749,10 @@ select jsonb_path_query('"12:34 -05:20"', '$.datetime("HH24:MI TZH:TZM")');
set time zone default;
select jsonb_path_query('"2023-08-15 12:34:56 +05:30"', '$.time()');
+select jsonb_path_query_tz('"2023-08-15 12:34:56 +05:30"', '$.time()'); -- should work
select jsonb_path_query('"2023-08-15 12:34:56 +05:30"', '$.time_tz()');
select jsonb_path_query('"2023-08-15 12:34:56 +05:30"', '$.timestamp()');
+select jsonb_path_query_tz('"2023-08-15 12:34:56 +05:30"', '$.timestamp()'); -- should work
select jsonb_path_query('"2023-08-15 12:34:56 +05:30"', '$.timestamp_tz()');
select jsonb_path_query('"2017-03-10"', '$.datetime().type()');
@@ -852,6 +865,9 @@ select jsonb_path_query(
select jsonb_path_query(
'["12:34:00", "12:35:00", "12:36:00", "12:35:00+00", "12:35:00+01", "13:35:00+01", "2017-03-10 12:35:00", "2017-03-10 12:35:00+01"]',
'$[*].time() ? (@ < "12:35:00".time())');
+select jsonb_path_query(
+ '["12:34:00.123", "12:35:00.123", "12:36:00.1123", "12:35:00.1123+00", "12:35:00.123+01", "13:35:00.123+01", "2017-03-10 12:35:00.1", "2017-03-10 12:35:00.123+01"]',
+ '$[*].time(2) ? (@ >= "12:35:00.123".time(2))');
select jsonb_path_query_tz(
'["12:34:00", "12:35:00", "12:36:00", "12:35:00+00", "12:35:00+01", "13:35:00+01", "2017-03-10 12:35:00", "2017-03-10 12:35:00+01"]',
'$[*].time() ? (@ == "12:35:00".time())');
@@ -861,7 +877,7 @@ select jsonb_path_query_tz(
select jsonb_path_query_tz(
'["12:34:00", "12:35:00", "12:36:00", "12:35:00+00", "12:35:00+01", "13:35:00+01", "2017-03-10 12:35:00", "2017-03-10 12:35:00+01"]',
'$[*].time() ? (@ < "12:35:00".time())');
-select jsonb_path_query(
+select jsonb_path_query_tz(
'["12:34:00.123", "12:35:00.123", "12:36:00.1123", "12:35:00.1123+00", "12:35:00.123+01", "13:35:00.123+01", "2017-03-10 12:35:00.1", "2017-03-10 12:35:00.123+01"]',
'$[*].time(2) ? (@ >= "12:35:00.123".time(2))');
@@ -904,6 +920,9 @@ select jsonb_path_query(
select jsonb_path_query(
'["12:34:00+01", "12:35:00+01", "12:36:00+01", "12:35:00+02", "12:35:00-02", "10:35:00", "11:35:00", "12:35:00", "2017-03-10 12:35:00 +1"]',
'$[*].time_tz() ? (@ < "12:35:00 +1".time_tz())');
+select jsonb_path_query(
+ '["12:34:00.123+01", "12:35:00.123+01", "12:36:00.1123+01", "12:35:00.1123+02", "12:35:00.123-02", "10:35:00.123", "11:35:00.1", "12:35:00.123", "2017-03-10 12:35:00.123 +1"]',
+ '$[*].time_tz(2) ? (@ >= "12:35:00.123 +1".time_tz(2))');
select jsonb_path_query_tz(
'["12:34:00+01", "12:35:00+01", "12:36:00+01", "12:35:00+02", "12:35:00-02", "10:35:00", "11:35:00", "12:35:00", "2017-03-10 12:35:00 +1"]',
'$[*].time_tz() ? (@ == "12:35:00 +1".time_tz())');
@@ -913,7 +932,7 @@ select jsonb_path_query_tz(
select jsonb_path_query_tz(
'["12:34:00+01", "12:35:00+01", "12:36:00+01", "12:35:00+02", "12:35:00-02", "10:35:00", "11:35:00", "12:35:00", "2017-03-10 12:35:00 +1"]',
'$[*].time_tz() ? (@ < "12:35:00 +1".time_tz())');
-select jsonb_path_query(
+select jsonb_path_query_tz(
'["12:34:00.123+01", "12:35:00.123+01", "12:36:00.1123+01", "12:35:00.1123+02", "12:35:00.123-02", "10:35:00.123", "11:35:00.1", "12:35:00.123", "2017-03-10 12:35:00.123 +1"]',
'$[*].time_tz(2) ? (@ >= "12:35:00.123 +1".time_tz(2))');
@@ -955,6 +974,9 @@ select jsonb_path_query(
select jsonb_path_query(
'["2017-03-10 12:34:00", "2017-03-10 12:35:00", "2017-03-10 12:36:00", "2017-03-10 12:35:00+01", "2017-03-10 13:35:00+01", "2017-03-10 12:35:00-01", "2017-03-10", "2017-03-11"]',
'$[*].timestamp() ? (@ < "2017-03-10 12:35:00".timestamp())');
+select jsonb_path_query(
+ '["2017-03-10 12:34:00.123", "2017-03-10 12:35:00.123", "2017-03-10 12:36:00.1123", "2017-03-10 12:35:00.1123+01", "2017-03-10 13:35:00.123+01", "2017-03-10 12:35:00.1-01", "2017-03-10", "2017-03-11"]',
+ '$[*].timestamp(2) ? (@ >= "2017-03-10 12:35:00.123".timestamp(2))');
select jsonb_path_query_tz(
'["2017-03-10 12:34:00", "2017-03-10 12:35:00", "2017-03-10 12:36:00", "2017-03-10 12:35:00+01", "2017-03-10 13:35:00+01", "2017-03-10 12:35:00-01", "2017-03-10", "2017-03-11"]',
'$[*].timestamp() ? (@ == "2017-03-10 12:35:00".timestamp())');
@@ -964,7 +986,7 @@ select jsonb_path_query_tz(
select jsonb_path_query_tz(
'["2017-03-10 12:34:00", "2017-03-10 12:35:00", "2017-03-10 12:36:00", "2017-03-10 12:35:00+01", "2017-03-10 13:35:00+01", "2017-03-10 12:35:00-01", "2017-03-10", "2017-03-11"]',
'$[*].timestamp() ? (@ < "2017-03-10 12:35:00".timestamp())');
-select jsonb_path_query(
+select jsonb_path_query_tz(
'["2017-03-10 12:34:00.123", "2017-03-10 12:35:00.123", "2017-03-10 12:36:00.1123", "2017-03-10 12:35:00.1123+01", "2017-03-10 13:35:00.123+01", "2017-03-10 12:35:00.1-01", "2017-03-10", "2017-03-11"]',
'$[*].timestamp(2) ? (@ >= "2017-03-10 12:35:00.123".timestamp(2))');
@@ -1006,6 +1028,9 @@ select jsonb_path_query(
select jsonb_path_query(
'["2017-03-10 12:34:00+01", "2017-03-10 12:35:00+01", "2017-03-10 12:36:00+01", "2017-03-10 12:35:00+02", "2017-03-10 12:35:00-02", "2017-03-10 10:35:00", "2017-03-10 11:35:00", "2017-03-10 12:35:00", "2017-03-10", "2017-03-11"]',
'$[*].timestamp_tz() ? (@ < "2017-03-10 12:35:00 +1".timestamp_tz())');
+select jsonb_path_query(
+ '["2017-03-10 12:34:00.123+01", "2017-03-10 12:35:00.123+01", "2017-03-10 12:36:00.1123+01", "2017-03-10 12:35:00.1123+02", "2017-03-10 12:35:00.123-02", "2017-03-10 10:35:00.123", "2017-03-10 11:35:00.1", "2017-03-10 12:35:00.123", "2017-03-10", "2017-03-11"]',
+ '$[*].timestamp_tz(2) ? (@ >= "2017-03-10 12:35:00.123 +1".timestamp_tz(2))');
select jsonb_path_query_tz(
'["2017-03-10 12:34:00+01", "2017-03-10 12:35:00+01", "2017-03-10 12:36:00+01", "2017-03-10 12:35:00+02", "2017-03-10 12:35:00-02", "2017-03-10 10:35:00", "2017-03-10 11:35:00", "2017-03-10 12:35:00", "2017-03-10", "2017-03-11"]',
'$[*].timestamp_tz() ? (@ == "2017-03-10 12:35:00 +1".timestamp_tz())');
@@ -1015,7 +1040,7 @@ select jsonb_path_query_tz(
select jsonb_path_query_tz(
'["2017-03-10 12:34:00+01", "2017-03-10 12:35:00+01", "2017-03-10 12:36:00+01", "2017-03-10 12:35:00+02", "2017-03-10 12:35:00-02", "2017-03-10 10:35:00", "2017-03-10 11:35:00", "2017-03-10 12:35:00", "2017-03-10", "2017-03-11"]',
'$[*].timestamp_tz() ? (@ < "2017-03-10 12:35:00 +1".timestamp_tz())');
-select jsonb_path_query(
+select jsonb_path_query_tz(
'["2017-03-10 12:34:00.123+01", "2017-03-10 12:35:00.123+01", "2017-03-10 12:36:00.1123+01", "2017-03-10 12:35:00.1123+02", "2017-03-10 12:35:00.123-02", "2017-03-10 10:35:00.123", "2017-03-10 11:35:00.1", "2017-03-10 12:35:00.123", "2017-03-10", "2017-03-11"]',
'$[*].timestamp_tz(2) ? (@ >= "2017-03-10 12:35:00.123 +1".timestamp_tz(2))');
Re: recently added jsonpath method change jsonb_path_query, jsonb_path_query_first immutability
On Thu, Feb 8, 2024 at 1:27 PM Jeevan Chalke
<jeevan.chalke@enterprisedb.com> wrote:
On Wed, Feb 7, 2024 at 9:13 PM jian he <jian.universality@gmail.com> wrote:
On Wed, Feb 7, 2024 at 7:36 PM Jeevan Chalke
<jeevan.chalke@enterprisedb.com> wrote:Added checkTimezoneIsUsedForCast() check where ever we are casting timezoned to non-timezoned types and vice-versa.
https://www.postgresql.org/docs/devel/functions-json.html
above Table 9.51. jsonpath Filter Expression Elements, the Note
section, do we also need to rephrase it?OK. Added a line for the same.
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 6788ba8..37ae2d1 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -18240,7 +18240,11 @@ ERROR: jsonpath member accessor can only be
applied to an object
<type>timestamptz</type>, and <type>time</type> to <type>timetz</type>.
However, all but the first of these conversions depend on the current
<xref linkend="guc-timezone"/> setting, and thus can only be performed
- within timezone-aware <type>jsonpath</type> functions.
+ within timezone-aware <type>jsonpath</type> functions. Similarly, other
+ date/time-related methods that convert string to the date/time types
+ also do the casting and may involve the current
+ <xref linkend="guc-timezone"/>. To preserve the immutability, those can
+ only be performed within timezone-aware <type>jsonpath</type> functions.
</para>
</note>my proposed minor changes:
- within timezone-aware <type>jsonpath</type> functions.
+ within timezone-aware <type>jsonpath</type> functions. Similarly, other
+ date/time-related methods that convert string to the date/time types
+ also do the casting and may involve the current
+ <xref linkend="guc-timezone"/> setting. Those conversions can
+ only be performed within timezone-aware <type>jsonpath</type> functions.
I don't have a strong opinion, though.Re: recently added jsonpath method change jsonb_path_query, jsonb_path_query_first immutability
On Thu, Feb 8, 2024 at 2:22 PM jian he <jian.universality@gmail.com> wrote:
On Thu, Feb 8, 2024 at 1:27 PM Jeevan Chalke
<jeevan.chalke@enterprisedb.com> wrote:On Wed, Feb 7, 2024 at 9:13 PM jian he <jian.universality@gmail.com>
wrote:
On Wed, Feb 7, 2024 at 7:36 PM Jeevan Chalke
<jeevan.chalke@enterprisedb.com> wrote:Added checkTimezoneIsUsedForCast() check where ever we are casting
timezoned to non-timezoned types and vice-versa.
https://www.postgresql.org/docs/devel/functions-json.html
above Table 9.51. jsonpath Filter Expression Elements, the Note
section, do we also need to rephrase it?OK. Added a line for the same.
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 6788ba8..37ae2d1 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -18240,7 +18240,11 @@ ERROR: jsonpath member accessor can only be applied to an object <type>timestamptz</type>, and <type>time</type> to <type>timetz</type>. However, all but the first of these conversions depend on the current <xref linkend="guc-timezone"/> setting, and thus can only be performed - within timezone-aware <type>jsonpath</type> functions. + within timezone-aware <type>jsonpath</type> functions. Similarly, other + date/time-related methods that convert string to the date/time types + also do the casting and may involve the current + <xref linkend="guc-timezone"/>. To preserve the immutability, those can + only be performed within timezone-aware <type>jsonpath</type> functions. </para> </note>my proposed minor changes: - within timezone-aware <type>jsonpath</type> functions. + within timezone-aware <type>jsonpath</type> functions. Similarly, other + date/time-related methods that convert string to the date/time types + also do the casting and may involve the current + <xref linkend="guc-timezone"/> setting. Those conversions can + only be performed within timezone-aware <type>jsonpath</type> functions. I don't have a strong opinion, though.
That seems fine as well. Let's leave that to the committer.
Thanks
--
Jeevan Chalke
*Principal, ManagerProduct Development*
edbpostgres.com
Re: recently added jsonpath method change jsonb_path_query, jsonb_path_query_first immutability
On 2024-02-08 Th 21:02, Jeevan Chalke wrote:
On Thu, Feb 8, 2024 at 2:22 PM jian he <jian.universality@gmail.com>
wrote:On Thu, Feb 8, 2024 at 1:27 PM Jeevan Chalke
<jeevan.chalke@enterprisedb.com> wrote:On Wed, Feb 7, 2024 at 9:13 PM jian he
<jian.universality@gmail.com> wrote:
On Wed, Feb 7, 2024 at 7:36 PM Jeevan Chalke
<jeevan.chalke@enterprisedb.com> wrote:Added checkTimezoneIsUsedForCast() check where ever we are
casting timezoned to non-timezoned types and vice-versa.
https://www.postgresql.org/docs/devel/functions-json.html
above Table 9.51. jsonpath Filter Expression Elements, the Note
section, do we also need to rephrase it?OK. Added a line for the same.
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 6788ba8..37ae2d1 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -18240,7 +18240,11 @@ ERROR: jsonpath member accessor can only be applied to an object <type>timestamptz</type>, and <type>time</type> to <type>timetz</type>. However, all but the first of these conversions depend on the current <xref linkend="guc-timezone"/> setting, and thus can only be performed - within timezone-aware <type>jsonpath</type> functions. + within timezone-aware <type>jsonpath</type> functions. Similarly, other + date/time-related methods that convert string to the date/time types + also do the casting and may involve the current + <xref linkend="guc-timezone"/>. To preserve the immutability, those can + only be performed within timezone-aware <type>jsonpath</type> functions. </para> </note>my proposed minor changes: - within timezone-aware <type>jsonpath</type> functions. + within timezone-aware <type>jsonpath</type> functions. Similarly, other + date/time-related methods that convert string to the date/time types + also do the casting and may involve the current + <xref linkend="guc-timezone"/> setting. Those conversions can + only be performed within timezone-aware <type>jsonpath</type> functions. I don't have a strong opinion, though.That seems fine as well. Let's leave that to the committer.
I edited slightly to my taste, and committed the patch. Thanks.
cheers
andrew
--
Andrew Dunstan
EDB:https://www.enterprisedb.com
Re: recently added jsonpath method change jsonb_path_query, jsonb_path_query_first immutability
On Sat, Feb 10, 2024 at 10:55 PM Andrew Dunstan <andrew@dunslane.net> wrote:
On 2024-02-08 Th 21:02, Jeevan Chalke wrote:
On Thu, Feb 8, 2024 at 2:22 PM jian he <jian.universality@gmail.com>
wrote:On Thu, Feb 8, 2024 at 1:27 PM Jeevan Chalke
<jeevan.chalke@enterprisedb.com> wrote:On Wed, Feb 7, 2024 at 9:13 PM jian he <jian.universality@gmail.com>
wrote:
On Wed, Feb 7, 2024 at 7:36 PM Jeevan Chalke
<jeevan.chalke@enterprisedb.com> wrote:Added checkTimezoneIsUsedForCast() check where ever we are casting
timezoned to non-timezoned types and vice-versa.
https://www.postgresql.org/docs/devel/functions-json.html
above Table 9.51. jsonpath Filter Expression Elements, the Note
section, do we also need to rephrase it?OK. Added a line for the same.
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 6788ba8..37ae2d1 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -18240,7 +18240,11 @@ ERROR: jsonpath member accessor can only be applied to an object <type>timestamptz</type>, and <type>time</type> to <type>timetz</type>. However, all but the first of these conversions depend on the current <xref linkend="guc-timezone"/> setting, and thus can only be performed - within timezone-aware <type>jsonpath</type> functions. + within timezone-aware <type>jsonpath</type> functions. Similarly, other + date/time-related methods that convert string to the date/time types + also do the casting and may involve the current + <xref linkend="guc-timezone"/>. To preserve the immutability, those can + only be performed within timezone-aware <type>jsonpath</type> functions. </para> </note>my proposed minor changes: - within timezone-aware <type>jsonpath</type> functions. + within timezone-aware <type>jsonpath</type> functions. Similarly, other + date/time-related methods that convert string to the date/time types + also do the casting and may involve the current + <xref linkend="guc-timezone"/> setting. Those conversions can + only be performed within timezone-aware <type>jsonpath</type> functions. I don't have a strong opinion, though.That seems fine as well. Let's leave that to the committer.
I edited slightly to my taste, and committed the patch. Thanks.
Thank you, Andrew and Jian.
cheers
andrew
--
Andrew Dunstan
EDB: https://www.enterprisedb.com
--
Jeevan Chalke
*Principal, ManagerProduct Development*
edbpostgres.com