Horton

without_patch.out

application/octet-stream

Download
Filename: without_patch.out
Type: application/octet-stream
Part: 5
Message: Re: remaining sql/json patches 
DROP TYPE IF EXISTS jbpop CASCADE;
DROP TYPE
CREATE TYPE jbpop AS (a text, b int, c timestamp);
CREATE TYPE
SELECT JSON_QUERY(jsonb'{"rec": "(abc,42,01.02.2003)"}', '$.rec' returning jbpop omit quotes);
 json_query 
------------
 
(1 row)

SELECT JSON_QUERY(jsonb'{"rec": "(abc,42,01.02.2003)"}', '$.rec' returning jbpop keep quotes);
 json_query 
------------
 
(1 row)

SELECT JSON_QUERY(jsonb'{"rec": "(abc,42,01.02.2003)"}', '$.rec' returning jbpop keep quotes error on error);
ERROR:  cannot call populate_composite on a scalar
SELECT JSON_QUERY(jsonb'{"rec": "{1,2,3}"}', '$.rec' returning int[] omit quotes);
 json_query 
------------
 
(1 row)

SELECT JSON_QUERY(jsonb'{"rec": "{1,2,3}"}', '$.rec' returning int[] keep quotes);
 json_query 
------------
 
(1 row)

SELECT JSON_QUERY(jsonb'{"rec": "{1,2,3}"}', '$.rec' returning int[] keep quotes error on error);
ERROR:  expected JSON array
SELECT JSON_QUERY(jsonb'{"rec": [1,2,3]}', '$.rec' returning int[] omit quotes);
 json_query 
------------
 {1,2,3}
(1 row)

SELECT JSON_QUERY(jsonb'{"rec": [1,2,3]}', '$.rec' returning int[] keep quotes);
 json_query 
------------
 {1,2,3}
(1 row)

SELECT JSON_QUERY(jsonb'{"rec": "[1,2]"}', '$.rec' returning int4range omit quotes);
 json_query 
------------
 [1,3)
(1 row)

SELECT JSON_QUERY(jsonb'{"rec": "[1,2]"}', '$.rec' returning int4range keep quotes);
 json_query 
------------
 [1,3)
(1 row)

SELECT JSON_QUERY(jsonb'{"rec": "[1,2]"}', '$.rec' 	returning int4range keep quotes error on error);
 json_query 
------------
 [1,3)
(1 row)

SELECT JSON_QUERY(jsonb'{"rec": "[1,2]"}', '$.rec' returning int4range);
 json_query 
------------
 [1,3)
(1 row)

SELECT JSON_QUERY(jsonb '"world"', '$' returning text WITH WRAPPER);
 json_query 
------------
 ["world"]
(1 row)

SELECT * FROM JSON_TABLE(jsonb '"12"', '$' COLUMNS (item numeric format json PATH '$' KEEP QUOTES ON SCALAR STRING));
ERROR:  cannot use JSON format with non-string output types
LINE 1: ...SON_TABLE(jsonb '"12"', '$' COLUMNS (item numeric format jso...
                                                             ^
SELECT * FROM JSON_TABLE(jsonb '"12"', '$' COLUMNS (item numeric format json PATH '$' OMIT QUOTES ON SCALAR STRING));
ERROR:  cannot use JSON format with non-string output types
LINE 1: ...SON_TABLE(jsonb '"12"', '$' COLUMNS (item numeric format jso...
                                                             ^
SELECT * FROM JSON_TABLE(jsonb '"12"', '$' COLUMNS (item numeric PATH '$' KEEP QUOTES ));
ERROR:  cannot use OMIT/KEEP QUOTES clause for scalar columns without also specifying FORMAT clause
LINE 1: ...LECT * FROM JSON_TABLE(jsonb '"12"', '$' COLUMNS (item numer...
                                                             ^
SELECT * FROM JSON_TABLE(jsonb '"12"', '$' COLUMNS (item numeric PATH '$' KEEP QUOTES error on error));
ERROR:  cannot use OMIT/KEEP QUOTES clause for scalar columns without also specifying FORMAT clause
LINE 1: ...LECT * FROM JSON_TABLE(jsonb '"12"', '$' COLUMNS (item numer...
                                                             ^
SELECT * FROM JSON_TABLE(jsonb '"12"', '$' COLUMNS (item numeric PATH '$' OMIT QUOTES ));
ERROR:  cannot use OMIT/KEEP QUOTES clause for scalar columns without also specifying FORMAT clause
LINE 1: ...LECT * FROM JSON_TABLE(jsonb '"12"', '$' COLUMNS (item numer...
                                                             ^
SELECT * FROM JSON_TABLE(jsonb'{"rec": [1,2,3]}', '$.rec' COLUMNS(v1 int[] 	path '$' keep quotes));
   v1    
---------
 {1,2,3}
(1 row)

SELECT * FROM JSON_TABLE(jsonb'{"rec": [1,2,3]}', '$.rec' COLUMNS(v1 int[] 	path '$' keep quotes error on error));
   v1    
---------
 {1,2,3}
(1 row)

SELECT * FROM JSON_TABLE(jsonb'{"rec": [1,2,3]}', '$.rec' COLUMNS(v1 int[] 	path '$' omit quotes));
   v1    
---------
 {1,2,3}
(1 row)

SELECT * from JSON_TABLE(jsonb'{"rec": "[1,2]"}', '$.rec' COLUMNS(v int4range FORMAT JSON path '$' keep quotes));
ERROR:  cannot use JSON format with non-string output types
LINE 1: ...nb'{"rec": "[1,2]"}', '$.rec' COLUMNS(v int4range FORMAT JSO...
                                                             ^
SELECT * from JSON_TABLE(jsonb'{"rec": "[1,2]"}', '$.rec' COLUMNS(v int4range FORMAT JSON path '$' omit quotes));
ERROR:  cannot use JSON format with non-string output types
LINE 1: ...nb'{"rec": "[1,2]"}', '$.rec' COLUMNS(v int4range FORMAT JSO...
                                                             ^
SELECT * from JSON_TABLE(jsonb'{"rec": "[1,2]"}', '$.rec' COLUMNS(v int4range path '$' keep quotes));
ERROR:  cannot use OMIT/KEEP QUOTES clause for scalar columns without also specifying FORMAT clause
LINE 1: ...ON_TABLE(jsonb'{"rec": "[1,2]"}', '$.rec' COLUMNS(v int4rang...
                                                             ^
SELECT * from JSON_TABLE(jsonb'{"rec": "[1,2]"}', '$.rec' COLUMNS(v int4range path '$' keep quotes error on error));
ERROR:  cannot use OMIT/KEEP QUOTES clause for scalar columns without also specifying FORMAT clause
LINE 1: ...ON_TABLE(jsonb'{"rec": "[1,2]"}', '$.rec' COLUMNS(v int4rang...
                                                             ^
SELECT * from JSON_TABLE(jsonb'{"rec": "[1,2]"}', '$.rec' COLUMNS(v int4range path '$' omit quotes));
ERROR:  cannot use OMIT/KEEP QUOTES clause for scalar columns without also specifying FORMAT clause
LINE 1: ...ON_TABLE(jsonb'{"rec": "[1,2]"}', '$.rec' COLUMNS(v int4rang...
                                                             ^
SELECT * from JSON_TABLE(jsonb'{"rec": "{1,2,3}"}', '$.rec' COLUMNS(v int[] path '$'  omit quotes));
 v 
---
 
(1 row)

SELECT * from JSON_TABLE(jsonb'{"rec": "{1,2,3}"}', '$.rec' COLUMNS(v int[] path '$'  keep quotes));
 v 
---
 
(1 row)

SELECT * from JSON_TABLE(jsonb'{"rec": "{1,2,3}"}', '$.rec' COLUMNS(v int[] path '$'  keep quotes error on error));
ERROR:  expected JSON array
SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a text EXISTS PATH '$.a' keep quotes));
ERROR:  syntax error at or near "keep"
LINE 1: ...sonb '"a"', '$' COLUMNS (a text EXISTS PATH '$.a' keep quote...
                                                             ^
SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a text EXISTS PATH '$.a' omit quotes));
ERROR:  syntax error at or near "omit"
LINE 1: ...sonb '"a"', '$' COLUMNS (a text EXISTS PATH '$.a' omit quote...
                                                             ^
SELECT * FROM JSON_TABLE(jsonb '"world"', '$' COLUMNS (item text PATH '$' WITH WRAPPER));
ERROR:  cannot use WITH WRAPPER clause for scalar columns without also specifying FORMAT clause
LINE 1: ...T * FROM JSON_TABLE(jsonb '"world"', '$' COLUMNS (item text ...
                                                             ^