The following bug has been logged on the website:
Bug reference: 9168
Logged by: Gary Teh
Email address: garyjob@krake.io
PostgreSQL version: 9.2.2
Operating system: MacOS X 10.7.5
Description:
Statement to create the database table
===
CREATE TABLE test_tables (
properties hstore,
"pingedAt" timestamp with time zone,
id integer NOT NULL,
"createdAt" timestamp with time zone NOT NULL,
"updatedAt" timestamp with time zone NOT NULL
);
Statement to create a record
===
INSERT INTO
"test_tables"
("createdAt","updatedAt","pingedAt","properties") VALUES
('2014-2-10 0:10:28','2014-2-10 0:10:28','2014-2-10 0:10:28','"drug bank" =>
"cache \"that stuff"')
Statement to export the record into a JSON file
===
Copy (
select
array_to_json(
array_agg( row_to_json(row)
)
)
from (
SELECT
properties::hstore->'drug bank' as "drug bank",
"pingedAt"
FROM
"test_tables"
WHERE true
) row
) To '/tmp/invalid_json_output'
The invalid JSON output
[{
"drug bank":"cache \\"that stuff",
"pingedAt":"2014-02-09 16:10:28-08"
},{
"drug bank":"cache \\"that stuff",
"pingedAt":"2014-02-10 00:10:28-08"
}]
The final JSON output is not valid.