Can't get UPSERT to work?

[domjammoo]

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:

Help?

[rbmike]

Hitting the exact same issue!

[justin]

Hey @domjammoo and @rbmike - my hunch here is that it’s doing this because it didn’t find a match for your filter by column. Could there be an issue with data types? If you run a regular Update query using these params, does it work?

[domjammoo]

Hey Justin. That’s what’s peculiar. I started with a query that was being used just for updates, with a separate insert query. So it works fine for updates on its own, and actually works correctly for updates when I do pass in a value for var_invoice_contract_id. In that case, the filter finds the record, and successfully performs an update.

For inserts, I have a separate JS query which calls this query with the same parameters but expressly does not pass any value for var_invoice_contract_id. It does correctly appear to have determined it should be an insert (see the highlighted error at the bottom), but then it is trying to insert a value into the primary key, which is not going to work. Additonally, if you look at the generated insert statement in the error message it includes the column ‘Invoice_contract_id’ (my primary key) even though this is not in the change set above. Looks like a bug?

I could get round this issue by using SQL mode and using ON CONFLICT but ironically that erodes some of the benefit as effectively I need to repeat the INSERT and UPDATE clauses. Using the GUI mode I’m trying to establish a standard template / pattern that I can use for all basic table CRUD updates and inserts, so am interested in getting the most efficient / lowest maintenance model before I start using it all over the place.

Thanks

[domjammoo]

1-week bump?

[justin]

Sorry for the delay here @domjammoo

I’m not sure if this is a bug, or a limitation of how we approach primary keys. Do you have your table set to autogenerate the primary key for each insert? What I think might be happening is that Retool is checking to see if your “filter” (i.e. pseudo primary key) matches any in the table, and if not, goes and inserts a full record including the filter column.

This obviously makes no sense for the user because in the upsert, there’s not way for you to specify the value of the primary key even if it wasn’t autogenerated. LMK and if so I’ll try and file this as a bug.

[domjammoo]

Hi Justin. So, yep, my table is set to GENERATE ALWAYS for the pk.

I think it probably would be classed as a bug bug but I can see why it is difficult; in my case (probably the base case), users would expect the pk to be set by the db. But technically your GUI is not specifying a pk, rather just a value to filter on, so there are scenarios where you would want to specify the filtered value. it could easily be a user field that needed to be unique. I guess it would need to have something like a checkbox for ‘set a value for this column on insert’ in the ideal world…

[aaron]

This is still an issue, when will it be fixed.. it makes no sense that the filter field for the upsert is added to the insert command ?.. Why would you automatically do that ?

[Kabirdas]

Hey @aaron, can check on this for you, for some additional context what kind of SQL resource are you using?

[efurban]

seeing the same issue here. using mssql. primary key is included in the insert query. how do I exclude it ?

[dcartlidge]

Feels like this one has slipped off the bug fix radar - any Upsert with an auto generated PK column fails because it tries to insert the PK. The workaround is to write your own exception handler in a regular SQL query or have 2 queries based on insert/update logic - both of which seem to negate the benefits of using a low code platform. Upsert function needs a "my table autogenerates the PK column" checkbox, imho

[domjammoo]

Will try and post a more complete workaround to this but my code is still a bit alpha, but based on what I'm doing in one of my apps at the moment I think the following works as a workaround:

• For the table to be updated, make sure that the primary key column is uneditable

• For the postgres update, use Bulk_upsert via a primary key to avoid having to specify all the fields, and specify an array variable to be passed in:
  

  

• create two save handlers, say: insert_sql and update_sql

• insert_sql will use .newRow, which will not include the primary key:
  

  

• update_sql will use recordUpdates, which will include the pk:
  

  

Not a lot of code and should generically work for any table / postgres query.

!Warning! I haven't actually tested this exact code, it's cut down from a more complex logic flow that I'm using based on the same principles. Pretty sure it should work, but you might need to fiddle about with what gets fed into x_array, like converting from object to array or something as recordUpdates is an array of objects, whereas newRow is just an array. Maybe newRow needs to get made into an array of objects with a single array entry.

Anyway hope that helps, will aim to post something more complete.

Thanks!