aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--doc/src/sgml/func.sgml16
-rw-r--r--src/test/regress/expected/horology.out9
-rw-r--r--src/test/regress/expected/jsonb_jsonpath.out103
-rw-r--r--src/test/regress/sql/horology.sql4
-rw-r--r--src/test/regress/sql/jsonb_jsonpath.sql22
5 files changed, 135 insertions, 19 deletions
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 461fc3f437c..1bde4091ca6 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -18016,7 +18016,9 @@ ERROR: jsonpath member accessor can only be applied to an object
<returnvalue><replaceable>string</replaceable></returnvalue>
</para>
<para>
- String value converted from a JSON boolean, number, string, or datetime
+ String value converted from a JSON boolean, number, string, or
+ datetime (the output format for datetimes is determined by
+ the <xref linkend="guc-datestyle"/> parameter)
</para>
<para>
<literal>jsonb_path_query_array('[1.23, "xyz", false]', '$[*].string()')</literal>
@@ -18105,7 +18107,9 @@ ERROR: jsonpath member accessor can only be applied to an object
<returnvalue><replaceable>decimal</replaceable></returnvalue>
</para>
<para>
- Rounded decimal value converted from a JSON number or string. <literal>precision</literal> and <literal>scale</literal> must be integer values.
+ Rounded decimal value converted from a JSON number or string
+ (<literal>precision</literal> and <literal>scale</literal> must be
+ integer values)
</para>
<para>
<literal>jsonb_path_query('1234.5678', '$.decimal(6, 2)')</literal>
@@ -18207,7 +18211,7 @@ ERROR: jsonpath member accessor can only be applied to an object
</para>
<para>
Time without time zone value converted from a string, with fractional
- seconds adjusted to the given precision.
+ seconds adjusted to the given precision
</para>
<para>
<literal>jsonb_path_query('"12:34:56.789"', '$.time(2)')</literal>
@@ -18236,7 +18240,7 @@ ERROR: jsonpath member accessor can only be applied to an object
</para>
<para>
Time with time zone value converted from a string, with fractional
- seconds adjusted to the given precision.
+ seconds adjusted to the given precision
</para>
<para>
<literal>jsonb_path_query('"12:34:56.789 +05:30"', '$.time_tz(2)')</literal>
@@ -18265,7 +18269,7 @@ ERROR: jsonpath member accessor can only be applied to an object
</para>
<para>
Timestamp without time zone value converted from a string, with
- fractional seconds adjusted to the given precision.
+ fractional seconds adjusted to the given precision
</para>
<para>
<literal>jsonb_path_query('"2023-08-15 12:34:56.789"', '$.timestamp(2)')</literal>
@@ -18294,7 +18298,7 @@ ERROR: jsonpath member accessor can only be applied to an object
</para>
<para>
Timestamp with time zone value converted from a string, with fractional
- seconds adjusted to the given precision.
+ seconds adjusted to the given precision
</para>
<para>
<literal>jsonb_path_query('"2023-08-15 12:34:56.789 +05:30"', '$.timestamp_tz(2)')</literal>
diff --git a/src/test/regress/expected/horology.out b/src/test/regress/expected/horology.out
index 241713cc51e..58da26ab0e1 100644
--- a/src/test/regress/expected/horology.out
+++ b/src/test/regress/expected/horology.out
@@ -1,13 +1,18 @@
--
-- HOROLOGY
--
-SET DateStyle = 'Postgres, MDY';
-SHOW TimeZone; -- Many of these tests depend on the prevailing setting
+SHOW TimeZone; -- Many of these tests depend on the prevailing settings
TimeZone
----------
PST8PDT
(1 row)
+SHOW DateStyle;
+ DateStyle
+---------------
+ Postgres, MDY
+(1 row)
+
--
-- Test various input formats
--
diff --git a/src/test/regress/expected/jsonb_jsonpath.out b/src/test/regress/expected/jsonb_jsonpath.out
index e534ed96965..70eeb655a2a 100644
--- a/src/test/regress/expected/jsonb_jsonpath.out
+++ b/src/test/regress/expected/jsonb_jsonpath.out
@@ -2636,6 +2636,18 @@ select jsonb_path_query('[2, true]', '$.string()');
"true"
(2 rows)
+select jsonb_path_query_array('[1.23, "yes", false]', '$[*].string()');
+ jsonb_path_query_array
+--------------------------
+ ["1.23", "yes", "false"]
+(1 row)
+
+select jsonb_path_query_array('[1.23, "yes", false]', '$[*].string().type()');
+ jsonb_path_query_array
+--------------------------------
+ ["string", "string", "string"]
+(1 row)
+
select jsonb_path_query('"2023-08-15 12:34:56 +5:30"', '$.timestamp().string()');
ERROR: cannot convert value from timestamptz to timestamp without time zone usage
HINT: Use *_tz() function for time zone support.
@@ -2645,18 +2657,95 @@ select jsonb_path_query_tz('"2023-08-15 12:34:56 +5:30"', '$.timestamp().string(
"Tue Aug 15 00:04:56 2023"
(1 row)
-select jsonb_path_query_array('[1.23, "yes", false]', '$[*].string()');
- jsonb_path_query_array
---------------------------
- ["1.23", "yes", "false"]
+select jsonb_path_query('"2023-08-15 12:34:56"', '$.timestamp_tz().string()');
+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().string()'); -- should work
+ jsonb_path_query_tz
+--------------------------------
+ "Tue Aug 15 12:34:56 2023 PDT"
(1 row)
-select jsonb_path_query_array('[1.23, "yes", false]', '$[*].string().type()');
- jsonb_path_query_array
+select jsonb_path_query('"2023-08-15 12:34:56 +5:30"', '$.timestamp_tz().string()');
+ jsonb_path_query
--------------------------------
- ["string", "string", "string"]
+ "Tue Aug 15 00:04:56 2023 PDT"
+(1 row)
+
+select jsonb_path_query('"2023-08-15 12:34:56"', '$.timestamp().string()');
+ jsonb_path_query
+----------------------------
+ "Tue Aug 15 12:34:56 2023"
+(1 row)
+
+select jsonb_path_query('"12:34:56 +5:30"', '$.time_tz().string()');
+ jsonb_path_query
+------------------
+ "12:34:56+05:30"
+(1 row)
+
+select jsonb_path_query_tz('"12:34:56"', '$.time_tz().string()');
+ jsonb_path_query_tz
+---------------------
+ "12:34:56-07"
+(1 row)
+
+select jsonb_path_query('"12:34:56"', '$.time().string()');
+ jsonb_path_query
+------------------
+ "12:34:56"
+(1 row)
+
+select jsonb_path_query('"2023-08-15"', '$.date().string()');
+ jsonb_path_query
+------------------
+ "08-15-2023"
+(1 row)
+
+set datestyle = 'ISO';
+select jsonb_path_query_tz('"2023-08-15 12:34:56"', '$.timestamp_tz().string()');
+ jsonb_path_query_tz
+--------------------------
+ "2023-08-15 12:34:56-07"
+(1 row)
+
+select jsonb_path_query('"2023-08-15 12:34:56 +5:30"', '$.timestamp_tz().string()');
+ jsonb_path_query
+--------------------------
+ "2023-08-15 00:04:56-07"
+(1 row)
+
+select jsonb_path_query('"2023-08-15 12:34:56"', '$.timestamp().string()');
+ jsonb_path_query
+-----------------------
+ "2023-08-15 12:34:56"
+(1 row)
+
+select jsonb_path_query('"12:34:56 +5:30"', '$.time_tz().string()');
+ jsonb_path_query
+------------------
+ "12:34:56+05:30"
+(1 row)
+
+select jsonb_path_query_tz('"12:34:56"', '$.time_tz().string()');
+ jsonb_path_query_tz
+---------------------
+ "12:34:56-07"
+(1 row)
+
+select jsonb_path_query('"12:34:56"', '$.time().string()');
+ jsonb_path_query
+------------------
+ "12:34:56"
+(1 row)
+
+select jsonb_path_query('"2023-08-15"', '$.date().string()');
+ jsonb_path_query
+------------------
+ "2023-08-15"
(1 row)
+reset datestyle;
-- Test .time()
select jsonb_path_query('null', '$.time()');
ERROR: jsonpath item method .time() can only be applied to a string
diff --git a/src/test/regress/sql/horology.sql b/src/test/regress/sql/horology.sql
index e5cf12ff63d..0fe3c783e61 100644
--- a/src/test/regress/sql/horology.sql
+++ b/src/test/regress/sql/horology.sql
@@ -1,9 +1,9 @@
--
-- HOROLOGY
--
-SET DateStyle = 'Postgres, MDY';
-SHOW TimeZone; -- Many of these tests depend on the prevailing setting
+SHOW TimeZone; -- Many of these tests depend on the prevailing settings
+SHOW DateStyle;
--
-- Test various input formats
diff --git a/src/test/regress/sql/jsonb_jsonpath.sql b/src/test/regress/sql/jsonb_jsonpath.sql
index 17f9d038c0a..4d57e13eda0 100644
--- a/src/test/regress/sql/jsonb_jsonpath.sql
+++ b/src/test/regress/sql/jsonb_jsonpath.sql
@@ -598,10 +598,28 @@ select jsonb_path_query('1234', '$.string()');
select jsonb_path_query('true', '$.string()');
select jsonb_path_query('1234', '$.string().type()');
select jsonb_path_query('[2, true]', '$.string()');
-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()');
+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('"2023-08-15 12:34:56"', '$.timestamp_tz().string()');
+select jsonb_path_query_tz('"2023-08-15 12:34:56"', '$.timestamp_tz().string()'); -- should work
+select jsonb_path_query('"2023-08-15 12:34:56 +5:30"', '$.timestamp_tz().string()');
+select jsonb_path_query('"2023-08-15 12:34:56"', '$.timestamp().string()');
+select jsonb_path_query('"12:34:56 +5:30"', '$.time_tz().string()');
+select jsonb_path_query_tz('"12:34:56"', '$.time_tz().string()');
+select jsonb_path_query('"12:34:56"', '$.time().string()');
+select jsonb_path_query('"2023-08-15"', '$.date().string()');
+
+set datestyle = 'ISO';
+select jsonb_path_query_tz('"2023-08-15 12:34:56"', '$.timestamp_tz().string()');
+select jsonb_path_query('"2023-08-15 12:34:56 +5:30"', '$.timestamp_tz().string()');
+select jsonb_path_query('"2023-08-15 12:34:56"', '$.timestamp().string()');
+select jsonb_path_query('"12:34:56 +5:30"', '$.time_tz().string()');
+select jsonb_path_query_tz('"12:34:56"', '$.time_tz().string()');
+select jsonb_path_query('"12:34:56"', '$.time().string()');
+select jsonb_path_query('"2023-08-15"', '$.date().string()');
+reset datestyle;
-- Test .time()
select jsonb_path_query('null', '$.time()');
generated by cgit v1.2.3 (git 2.25.1) at 2025-07-12 12:43:09 +0000