From ed055d249df577ab40470f9dc1a30ab18ffdfff4 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Tue, 10 Sep 2024 14:48:13 -0400 Subject: Improve documentation and testing of jsonpath string() for datetimes. Point out that the output format depends on DateStyle, and test that, along with testing some cases previously not covered. In passing, adjust the horology test to verify that the prevailing DateStyle is 'Postgres, MDY', much as it has long verified the prevailing TimeZone. We expect pg_regress to have set these up, and there are multiple regression tests relying on these settings. Also make the formatting of entries in table 9.50 more consistent. David Wheeler (marginal additional hacking by me); review by jian he Discussion: https://postgr.es/m/56955B33-6959-4FDA-A459-F00363ECDFEE@justatheory.com --- doc/src/sgml/func.sgml | 16 +++-- src/test/regress/expected/horology.out | 9 ++- src/test/regress/expected/jsonb_jsonpath.out | 103 +++++++++++++++++++++++++-- src/test/regress/sql/horology.sql | 4 +- src/test/regress/sql/jsonb_jsonpath.sql | 22 +++++- 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 string - 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 parameter) jsonb_path_query_array('[1.23, "xyz", false]', '$[*].string()') @@ -18105,7 +18107,9 @@ ERROR: jsonpath member accessor can only be applied to an object decimal - Rounded decimal value converted from a JSON number or string. precision and scale must be integer values. + Rounded decimal value converted from a JSON number or string + (precision and scale must be + integer values) jsonb_path_query('1234.5678', '$.decimal(6, 2)') @@ -18207,7 +18211,7 @@ ERROR: jsonpath member accessor can only be applied to an object Time without time zone value converted from a string, with fractional - seconds adjusted to the given precision. + seconds adjusted to the given precision jsonb_path_query('"12:34:56.789"', '$.time(2)') @@ -18236,7 +18240,7 @@ ERROR: jsonpath member accessor can only be applied to an object Time with time zone value converted from a string, with fractional - seconds adjusted to the given precision. + seconds adjusted to the given precision jsonb_path_query('"12:34:56.789 +05:30"', '$.time_tz(2)') @@ -18265,7 +18269,7 @@ ERROR: jsonpath member accessor can only be applied to an object Timestamp without time zone value converted from a string, with - fractional seconds adjusted to the given precision. + fractional seconds adjusted to the given precision jsonb_path_query('"2023-08-15 12:34:56.789"', '$.timestamp(2)') @@ -18294,7 +18298,7 @@ ERROR: jsonpath member accessor can only be applied to an object Timestamp with time zone value converted from a string, with fractional - seconds adjusted to the given precision. + seconds adjusted to the given precision jsonb_path_query('"2023-08-15 12:34:56.789 +05:30"', '$.timestamp_tz(2)') 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()'); -- cgit v1.2.3