I'm trying to Bulk Upsert Records in Retool DB Table with composite primary key using SQL (since GUI mode doesn't work with composite keys)
Query:
INSERT INTO product_cost_elements (variant_id, element_id, quantity, waste_modifier)
VALUES {{pce_table_changes.value.upsertValues}}
ON CONFLICT (variant_id, element_id)
DO UPDATE SET
quantity = EXCLUDED.quantity,
waste_modifier = EXCLUDED.waste_modifier;
Details: running this query returns this error message:
message:"syntax error at or near "$1""
but the 'query' attribute of the query object from the State menu is valid SQL:
INSERT INTO product_cost_elements (variant_id, element_id, quantity, waste_modifier)
VALUES ('pv_1w8xzkx1', 1, 66, '0.00'), ('pv_18erq5q1', 1, 66, '0.00'), ('pv_jzzn2vvj', 1, 66, '0.00'), ('pv_1l8q4z63', 1, 66, '0.00'), ('pv_3pq5z4l1', 1, 66, '0.00')
ON CONFLICT (variant_id, element_id)
DO UPDATE SET quantity = EXCLUDED.quantity, waste_modifier = EXCLUDED.waste_modifier;
If I paste that into the query it executes just fine. It only gives the syntax error when the variable is in it, even though the variable seems to be correct. Am I doing something wrong here?