Workflow retool database block jsonb error "invalid input syntax for type json" when trying to populate jsonb column

💬 Workflows
, ,
14

The SQL for a multi-value insert like what you are doing is

INSERT INTO xyz (col1, col2)
VALUES ('a','b'),('c','d');

The {{code1.data}} is a JSON object, not a bunch of JSON objects surrounded by ( ). The simple INSERT of a single value works because you are putting the first object {{code1.data[0}} inside ( ) hich is the right format.

Since you probably have a variable number of results (and it would be annoying), you don't want to write something like:

INSERT INTO 
  ssa_inventory (data)
VALUES 
  ({{code1.data[0]}}),
  ({{code1.data[1]}}),
  etc...

And so you need switch it up to a SELECT query (as it seems you successfully did).

2 Likes