Postgres: unterminated quoted string at or near using psycopg2

Question

I have a postgres database with two tables stream_239_1_1_1_1234_0 and stream_239_0_0_40_1234_0. Each table has one column named alarm. I'm saving data into these tables using psycopg2 Python client.

I have no problem inserting data into stream_239_1_1_1_1234_0 as follows:

INSERT INTO stream_239_1_1_1_1234_0 (alarm) VALUES ('{"delay_max":
0.0, "ts_errors": [{"count": 0, "state": 0, "is_priority1": true, "name": "SYNC", "is_priority2": false}, {"count": 0, "state": 0,
"is_priority1": true, "name": "BYTE", "is_priority2": false},
{"count": 0, "state": 0, "is_priority1": true, "name": "PAT",
"is_priority2": false}, {"count": 0, "state": 0, "is_priority1": true,
"name": "CC", "is_priority2": false}, {"count": 0, "state": 0,
"is_priority1": true, "name": "PMT", "is_priority2": false}, {"count":
0, "state": 0, "is_priority1": true, "name": "PID", "is_priority2":
false}, {"count": 0, "state": 0, "is_priority1": false, "name": "TS",
"is_priority2": true}, {"count": 0, "state": 0, "is_priority1": false,
"name": "CRC", "is_priority2": true}, {"count": 0, "state": 0,
"is_priority1": false, "name": "PCR", "is_priority2": true}, {"count":
0, "state": 0, "is_priority1": false, "name": "ACC", "is_priority2":
true}, {"count": 0, "state": 0, "is_priority1": false, "name": "PTS",
"is_priority2": true}, {"count": 0, "state": 0, "is_priority1": false,
"name": "CAT", "is_priority2": true}], "is_stream_paused": false,
"delay_min": 0.0, "ac_err": 0.0, "oj_err":
0.0,"ipm_errors":[{"mnv":-3017, "mxv":2996, "mev":0, "mi":1000, "cr":0, "Bps":276667.104687, "df":4919, "pkl":0, "oos":0, "rei":0,
"reo":0, "ncl":0.0, "slf":0.0, "mtl":0.0, "ld":0, "lpl":0, "lr":0.0}],
"timestamp": "2017:02:16:16:10:49","id3_data": "None"}');

Whereas I get this error with this query

INSERT INTO stream_239_0_0_40_1234_0 (alarm) VALUES ('{"delay_max":
0.0, "ts_errors": [{"count": 0, "state": 0, "is_priority1": true, "name": "SYNC", "is_priority2": false}, {"count": 0, "state": 0,
"is_priority1": true, "name": "BYTE", "is_priority2": false},
{"count": 0, "state": 0, "is_priority1": true, "name": "PAT",
"is_priority2": false}, {"count": 0, "state": 0, "is_priority1": true,
"name": "CC", "is_priority2": false}, {"count": 0, "state": 0,
"is_priority1": true, "name": "PMT", "is_priority2": false}, {"count":
0, "state": 0, "is_priority1": true, "name": "PID", "is_priority2":
false}, {"count": 0, "state": 0, "is_priority1": false, "name": "TS",
"is_priority2": true}, {"count": 0, "state": 0, "is_priority1": false,
"name": "CRC", "is_priority2": true}, {"count": 0, "state": 0,
"is_priority1": false, "name": "PCR", "is_priority2": true}, {"count":
0, "state": 0, "is_priority1": false, "name": "ACC", "is_priority2":
true}, {"count": 0, "state": 0, "is_priority1": false, "name": "PTS",
"is_priority2": true}, {"count": 0, "state": 0, "is_priority1": false,
"name": "CAT", "is_priority2": true}], "is_stream_paused": false,
"delay_min": 0.0, "ac_err": 0.0, "oj_err":
0.0,"ipm_errors":[{"mnv":-140, "mxv":143, "mev":0, "mi":1000, "cr":0, "Bps":697856.570014, "df":0, "pkl":0, "oos":0, "rei":0, "reo":0,
"ncl":0.0, "slf":0.0, "mtl":0.0, "ld":0, "lpl":0, "lr":0.0}],
"timestamp": "2017:02:16:16:16:43","id3_data": [{"Payload":
"2016:05:12T11:47:41:000z", "id3_timestamp":
"2017:02:16:21:16:40"}]}');

The error is :

unterminated quoted string at or near "'{"delay_max": 0.0, "ts_errors": [{"count": 0, "state": 0, "is_priority1": true, "name": "SYNC", "is_priority2": false}, {"count": 0, "state": 0, "is_priority1": true, "name": "BYTE", "is_priority2": false}, {"count": 0, "state": 0, "is_priority1": true, "name": "PAT", "is_priority2": false}, {"count": 0, "state": 0, "is_priority1": true, "name": "CC", "is_priority2": false}, {"count": 0, "state": 0, "is_priority1": true, "name": "PMT", "is_priority2": false}, {"count": 0, "state": 0, "is_priority1": true, "name": "PID", "is_priority2": false}, {"count": 0, "state": 0, "is_priority1": false, "name": "TS", "is_priority2": true}, {"count": 0, "state": 0, "is_priority1": false, "name": "CRC", "is_priority2": true}, {"count": 0, "state": 0, "is_priority1": false, "name": "PCR", "is_priority2": true}, {"count": 0, "state": 0, "is_priority1": false, "name": "ACC", "is_priority2": true}, {"count": 0, "state": 0, "is_priority1": false, "name": "PTS", "is_priority2": true}, {"count": 0, "state": 0, "is_priority1": false, "name": "CAT", "is_priority2": true}], "is_stream_paused": false, "delay_min": 0.0, "ac_err": 0.0, "oj_err": 0.0,"ipm_errors":[{"mnv":-140, "mxv":143, "mev":0, "mi":1000, "cr":0, "Bps":697856.570014, "df":0, "pkl":0, "oos":0, "rei":0, "reo":0, "ncl":0.0, "slf":0.0, "mtl":0.0, "ld":0, "lpl":0, "lr":0.0}], "timestamp": "2017:02:16:16:16:43","id3_data": [{"Payload": "2016:05:12T11:47:41:000z" LINE 1: ...ERT INTO stream_239_0_0_40_1234_0 (alarm) VALUES ('{"delay_m...

If I try to manually run the query from postgres shell, it works fine! I had a look on this question but I'm not sure how is it relevant to my case. The psycopg2 Python client works fine already with the first query!

Any suggestions please?

EDIT {"ac_err": 0.0, "oj_err": 0.0, "id3_data": [{"Payload": "2016:05:12T11:47:41:000z", "id3_timestamp": "2017:02:16:21:16:40"}], "delay_max": 0.0, "delay_min": 0.0, "timestamp": "2017:02:16:16:16:43", "ts_errors": [{"name": "SYNC", "count": 0, "state": 0, "is_priority1": true, "is_priority2": false}, {"name": "BYTE", "count": 0, "state": 0, "is_priority1": true, "is_priority2": false}, {"name": "PAT", "count": 0, "state": 0, "is_priority1": true, "is_priority2": false}, {"name": "CC", "count": 0, "state": 0, "is_priority1": true, "is_priority2": false}, {"name": "PMT", "count": 0, "state": 0, "is_priority1": true, "is_priority2": false}, {"name": "PID", "count": 0, "state": 0, "is_priority1": true, "is_priority2": false}, {"name": "TS", "count": 0, "state": 0, "is_priority1": false, "is_priority2": true}, {"name": "CRC", "count": 0, "state": 0, "is_priority1": false, "is_priority2": true}, {"name": "PCR", "count": 0, "state": 0, "is_priority1": false, "is_priority2": true}, {"name": "ACC", "count": 0, "state": 0, "is_priority1": false, "is_priority2": true}, {"name": "PTS", "count": 0, "state": 0, "is_priority1": false, "is_priority2": true}, {"name": "CAT", "count": 0, "state": 0, "is_priority1": false, "is_priority2": true}], "ipm_errors": [{"cr": 0, "df": 0, "ld": 0, "lr": 0.0, "mi": 1000, "Bps": 697856.570014, "lpl": 0, "mev": 0, "mnv": -140, "mtl": 0.0, "mxv": 143, "ncl": 0.0, "oos": 0, "pkl": 0, "rei": 0, "reo": 0, "slf": 0.0}], "is_stream_paused": false}

EDIT I found the answer and I will post it

Answer 1

The problem was basically in the encoding of the string forming the JSON in "Payload": "2016:05:12T11:47:41:000z".

This payload is coming from a C++ function and it's being passed to the Python side. It represents packet data. For some reason, the payload had a weird encoding. Although I made sure that the Payload was in the string format and the object was in a valid JSON format, the psycopg2 couldn't recognize this encoding and that justifies why I was able to manually run the query. I fixed the problem by getting rid of Null terminating character at the end of the Payload data