Can't get UPSERT to work?

How do I?
#1

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:

image
image875×480 44.3 KB

Help?

1 Like
#2

Hitting the exact same issue!

#3

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?

#4

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

#5

1-week bump?

#6

Sorry for the delay here @domjammoo :confused:

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.

#7

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…