Syntax error in my SQL when inserting a JSON field-CodePudding

Why this query is not working:

UPDATE country SET timezones="[{"zoneName":'Asia\/Kabul',"gmtOffset":16200,"gmtOffsetName":'UTC 04:30',"abbreviation":'AFT',"tzName":'Afghanistan Time'}] " where name='Afghanistan'

Error I get:

ERROR: syntax error at or near "zoneName" LINE 1: UPDATE country SET timezones="[{"zoneName":'Asia/Kabul',"gm... ^ SQL state: 42601 Character: 34

CodePudding user response：

the issue with your SQL statement is that the literal string you are trying to set timezones to contains improperly formatted escape characters. if you wanted to avoid that first error you can double up on quotes like timezones="[{""zoneName"": ...

you can go to the link to see more about string formating in SQL. good luck!

CodePudding user response：

You're trying to update the value wrapping the string in quotes. You need to wrap the string in single quotes timezones='[{"zoneName":'Asia...}]'

However, to TitledTeapot's point, you will also have to escape the existing single quotes in your string, so you'd end up with something like this:

'[{"zoneName":''Asia\/Kabul'',"gmtOffset":16200,"gmtOffsetName":''UTC 04:30'',"abbreviation":''AFT'',"tzName":''Afghanistan Time''}]'