On Jan 19, 2024, at 21:46, Erik Wienhold <e...@ewie.name> wrote:
> Interesting... copy-pasting the entire \set command works for me with
> psql 16.1 in gnome-terminal and tmux. Typing it out manually gives me
> the "unterminated quoted string" error. Maybe has to do with my stty
> settings.
Yes, same on macOS Terminal.app and 16.1 compiled with readline. I didn’t
realize that \set didn’t support newlines, because it works fine when you paste
something with newlines. Curious.
>> I experimented with
>>
>> SELECT '
>> ... multiline json value ...
>> ' AS json
>> \gexec
>>
>> but that didn't seem to work either. Anybody have a better idea?
>
> Fine with me (the \gset variant).
Much cleaner TBH.
david=# select '{
"track": {
"segments": [
{
"location": [ 47.763, 13.4034 ],
"start time": "2018-10-14 10:05:14",
"HR": 73
},
{
"location": [ 47.706, 13.2635 ],
"start time": "2018-10-14 10:39:21",
"HR": 135
}
]
}
}'::jsonb as json;
json
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
{"track": {"segments": [{"HR": 73, "location": [47.763, 13.4034], "start
time": "2018-10-14 10:05:14"}, {"HR": 135, "location": [47.706, 13.2635],
"start time": "2018-10-14 10:39:21"}]}}
(1 row)
david=# \gset
david=# select :'json'::jsonb;
jsonb
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
{"track": {"segments": [{"HR": 73, "location": [47.763, 13.4034], "start
time": "2018-10-14 10:05:14"}, {"HR": 135, "location": [47.706, 13.2635],
"start time": "2018-10-14 10:39:21"}]}}
(1 row)
So great!
While you’re in there, Tom, would it make sense to fold in something like [this
patch][1] I posted last month to clarify which JSONPath comparison operators
can take advantage of a index?
--- a/doc/src/sgml/json.sgml
+++ b/doc/src/sgml/json.sgml
@@ -513,7 +513,7 @@ SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @@
'$.tags[*] == "qui"';
</programlisting>
For these operators, a GIN index extracts clauses of the form
<literal><replaceable>accessors_chain</replaceable>
- = <replaceable>constant</replaceable></literal> out of
+ == <replaceable>constant</replaceable></literal> out of
the <type>jsonpath</type> pattern, and does the index search based on
the keys and values mentioned in these clauses. The accessors chain
may include <literal>.<replaceable>key</replaceable></literal>,
@@ -522,6 +522,9 @@ SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @@
'$.tags[*] == "qui"';
The <literal>jsonb_ops</literal> operator class also
supports <literal>.*</literal> and <literal>.**</literal> accessors,
but the <literal>jsonb_path_ops</literal> operator class does not.
+ Only the <literal>==</literal> and <literal>!=</literal> <link
+ linkend="functions-sqljson-path-operators">SQL/JSON Path Operators</link>
+ can use the index.
</para>
<para>
Best,
David
[1]:
https://www.postgresql.org/message-id/0ece6b9c-cdde-4b65-be5a-49d737204...@justatheory.com
