Syntax error in my SQL when inserting a JSON field

Question

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

SQL string literals are marked with single quotes, JSON string literals are marked with double quotes... '[{"key1": "value1", "key2": "value2"}, {etc...}]' — MatBailie
– MatBailie, Commented Aug 11, 2022 at 21:23

TiltedTeapot · Accepted Answer · 2022-08-11 21:12:40Z

1

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!

answered Aug 11, 2022 at 21:12

TiltedTeapot

814 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

MatBailie · Accepted Answer · 2022-08-11 21:27:35Z

1

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''}]'

edited Aug 11, 2022 at 21:27

MatBailie

87.4k19 gold badges112 silver badges144 bronze badges

answered Aug 11, 2022 at 21:21

T-Rev

1721 silver badge8 bronze badges

Collectives™ on Stack Overflow

Syntax error in my SQL when inserting a JSON field

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related