Casting hstore to json
Hi.
I'm trying to cast hstore to json, but I don't seem to be getting a json
object from hstore key/value pairs.
9.3 documentation says:
*Note:* The hstore
<http://www.postgresql.org/docs/9.3/static/hstore.html> extension
has a cast from hstore to json, so that converted hstore values are
represented as JSON objects, not as string values.
to_json(anyelement) - If the data type is not built in, and there is a cast
from the type tojson, the cast function will be used to perform the
conversion.
On 9.3.5 server:
db=> SELECT '"a"=>1, "b"=>2'::hstore;
hstore
--------------------
"a"=>"1", "b"=>"2"
(1 row)
db=> SELECT to_json('"a"=>1, "b"=>2'::hstore);
to_json
------------------------------
"\"a\"=>\"1\", \"b\"=>\"2\""
(1 row)
db=> SELECT cast(('"a"=>1, "b"=>2'::hstore) as json);
ERROR: cannot cast type hstore to json
LINE 1: SELECT cast(('"a"=>1, "b"=>2'::hstore) as json);
Thanks!
Pawel.
On 01/15/2015 07:59 PM, Pawel Veselov wrote:
Hi.
I'm trying to cast hstore to json, but I don't seem to be getting a json
object from hstore key/value pairs.9.3 documentation says:
*Note:* The hstore
<http://www.postgresql.org/docs/9.3/static/hstore.html> extension has a
cast from hstore to json, so that converted hstore values are
represented as JSON objects, not as string values.
to_json(anyelement) - If the data type is not built in, and there is a
cast from the type tojson, the cast function will be used to perform the
conversion.On 9.3.5 server:
db=> SELECT '"a"=>1, "b"=>2'::hstore;
hstore
--------------------
"a"=>"1", "b"=>"2"
(1 row)db=> SELECT to_json('"a"=>1, "b"=>2'::hstore);
to_json
------------------------------
"\"a\"=>\"1\", \"b\"=>\"2\""
Works here:
business_app=> select version();
version
-----------------------------------------------------------------------------------------------------------------------------
PostgreSQL 9.3.5 on i686-pc-linux-gnu, compiled by gcc (SUSE Linux)
4.8.1 20130909 [gcc-4_8-branch revision 202388], 32-bit
(1 row)
business_app=> SELECT to_json('"a"=>1, "b"=>2'::hstore);
to_json
----------------------
{"a": "1", "b": "2"}
(1 row)
Did you upgrade from a previous version of Postgres?
(1 row)
db=> SELECT cast(('"a"=>1, "b"=>2'::hstore) as json);
ERROR: cannot cast type hstore to json
LINE 1: SELECT cast(('"a"=>1, "b"=>2'::hstore) as json);Thanks!
Pawel.
--
Adrian Klaver
adrian.klaver@aklaver.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Adrian,
On Thu, Jan 15, 2015 at 9:09 PM, Adrian Klaver <adrian.klaver@aklaver.com>
wrote:
On 01/15/2015 07:59 PM, Pawel Veselov wrote:
Hi.
I'm trying to cast hstore to json, but I don't seem to be getting a json
object from hstore key/value pairs.
[skipped]
db=> SELECT to_json('"a"=>1, "b"=>2'::hstore);
to_json
------------------------------
"\"a\"=>\"1\", \"b\"=>\"2\""Works here:
business_app=> select version();
version
------------------------------------------------------------
-----------------------------------------------------------------
PostgreSQL 9.3.5 on i686-pc-linux-gnu, compiled by gcc (SUSE Linux) 4.8.1
20130909 [gcc-4_8-branch revision 202388], 32-bit
(1 row)business_app=> SELECT to_json('"a"=>1, "b"=>2'::hstore);
to_json
----------------------
{"a": "1", "b": "2"}
(1 row)Did you upgrade from a previous version of Postgres?
Yes! From 9.1.14. 'alter extension hstore update' did the trick, thank you
:)
[skipped]