I have jsonb[] column and it allows me to insert some nested arrays, but only with number values like {{1,2,3}, {4,5,6}, {7,8,9}}. I'm not talking about inserting some nested objects like {{"one": "two"}, {"three": "four"}}.

How to even insert nested string values like:

{ {"one", "two", "three"},
  {"four", "five", "six"},
  {"seven", "eight", "nine"} }

enter image description here

ERROR: invalid syntax..., token "one" invalid

  • For arrays, you have to use square brachets [ ... ], not braces { ... }. – Laurenz Albe Aug 7 at 14:35
  • @LaurenzAlbe no, I haven't to. Postgres require curly brackets for array annotation. – khex Aug 7 at 14:48
  • 2
    I misread. But the array elements are jsomb arrays, so they have to be surrounded by brackets (and quoted, as in {"[1,2,3]","[4,5,6]"}) . – Laurenz Albe Aug 7 at 14:59

The answer is: in pgadmin you have to escape every quotation mark before and after key/value definition. What a sophisticated solution.

    "{\"one\": \"two\"}",
    "{\"three\": \"foure\"}",
    "{\"fivep\": \"six\"}"

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.