Hi Retool folk.
So for efficiency, I want to use on UPSERT query (or rather, “Update a record, or create a new record if it doesn’t exist”).
I have two JS queries, one for update and one for new. The update sets a bunch of additional scope variables taken from table.recordUpdates; the new one just sets those vars straight from table.newRow, both of them then call the same GUI mode SQL query. So far so good.
The update works fine.
The insert does not. It correctly runs the right trigger and attempts to perform an insert, but then fails as it is trying to insert a value into the PostgreSQL, autogenerated primary key column. I have tried every combination I can think of (not defining it, setting it to null etc) but still get the same issue.
I would have thought that, fundamentally, an UPSERT would not try and insert a value into the field specified in the ‘filter by’ section (usually the primary key)if it was inserting?
See this pic: