Workflow SQL INSERT INTO error: syntax error at or near "$1"

  • Goal: I'm trying to bulk insert records into a retool database. I transformed the data to be a nested array. I'm currently testing with only 2 columns.

  • Steps: Get data: check, transform data: check, Insert data: problem

  • Details: I'm trying to insert an nested array of data into a retool database from a workflow. I'm currently using SQL syntax for the insert. I suspect I'm using the retool variable the wrong way but I cant figure out how to use it the right way. I'm getting the error: syntax error at or near "$1". When I look at the result I'm trying to input into my database on its own it is a nested array of data. But when its in the query it shows as 1 giant array.

  • Screenshots:

  • App json export: {"data":null,"error":{"error":true,"message":"syntax error at or near \"$1\"","position":57,"isRetoolSystemError":false,"queryExecutionMetadata":{"estimatedResponseSizeBytes":99,"resourceTimeTakenMs":97,"isPreview":false,"resourceType":"retoolDb","lastReceivedFromResourceAt":1712761726485}}}

Funny - I just wrote about this in another post. Partially quoting myself, the SQL for a multi-value insert like what you are doing is

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

The query you are writing is not this form you have something like INSERT INTO xyz (object).

What you are likely looking to do is

INSERT INTO today_prices (cost_time, price_stock)
/* put your query4 SELECT statement here */
1 Like

Hi @jg80

Thanx for your response! Sorry for antoher question but i'm trying to understand this. In my screenshots it looks to me that I have the correct structure for the regular:

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

I'm getting the data I want to insert from a api endpoint. I'm then looping trough the data to build up the correct structure for the insert.
Can you see how this is confusing me?

The values as I see them in your screenshot are not a text string in the (a,b),(c,d) format, but instead an array of arrays [ [a,b] , [c,d] ].