Horton

with_patch.out

application/octet-stream

Download
Filename: with_patch.out
Type: application/octet-stream
Part: 6
Message: Re: remaining sql/json patches 
DROP TYPE IF EXISTS jbpop CASCADE;
NOTICE:  type "jbpop" does not exist, skipping
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           
--------------------------------
 (abc,42,"2003-01-02 00:00:00")
(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:  malformed record literal: ""(abc,42,01.02.2003)""
DETAIL:  Missing left parenthesis.
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 row)

SELECT JSON_QUERY(jsonb'{"rec": "{1,2,3}"}', '$.rec' returning int[] keep quotes error on error);
ERROR:  malformed array literal: ""{1,2,3}""
DETAIL:  Array value must start with "{" or dimension information.
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 row)

SELECT JSON_QUERY(jsonb'{"rec": "[1,2]"}', '$.rec' 	returning int4range keep quotes error on error);
ERROR:  malformed range literal: ""[1,2]""
DETAIL:  Missing left parenthesis or bracket.
SELECT JSON_QUERY(jsonb'{"rec": "[1,2]"}', '$.rec' returning int4range);
 json_query 
------------
 
(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 ));
 item 
------
     
(1 row)

SELECT * FROM JSON_TABLE(jsonb '"12"', '$' COLUMNS (item numeric PATH '$' KEEP QUOTES error on error));
ERROR:  invalid input syntax for type numeric: ""12""
SELECT * FROM JSON_TABLE(jsonb '"12"', '$' COLUMNS (item numeric PATH '$' OMIT QUOTES ));
 item 
------
   12
(1 row)

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));
 v 
---
 
(1 row)

SELECT * from JSON_TABLE(jsonb'{"rec": "[1,2]"}', '$.rec' COLUMNS(v int4range path '$' keep quotes error on error));
ERROR:  malformed range literal: ""[1,2]""
DETAIL:  Missing left parenthesis or bracket.
SELECT * from JSON_TABLE(jsonb'{"rec": "[1,2]"}', '$.rec' COLUMNS(v int4range path '$' omit quotes));
   v   
-------
 [1,3)
(1 row)

SELECT * from JSON_TABLE(jsonb'{"rec": "{1,2,3}"}', '$.rec' COLUMNS(v int[] path '$'  omit quotes));
    v    
---------
 {1,2,3}
(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:  malformed array literal: ""{1,2,3}""
DETAIL:  Array value must start with "{" or dimension information.
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));
   item    
-----------
 ["world"]
(1 row)