wonder if it's to do with undefined vs. null:
Hey @dhretool!
Would you mind posting the configuration of that column? You can grab it with the following query:
SELECT
*
FROM
information_schema.columns
WHERE
table_name = 'MozendaData_Retool' and column_name = 'id';
This should be working for Retool Database, hopefully that context helps identify where things might be going wrong.
not sure what the issue is here, I am seeing the behavior on my end but am not able to consistently reproduce it. In testing an id column that matches your settings seems to be working correctly.
Will keep trying to reproduce and keep you updated! Had this been working before?
In the meantime, you might explore using a generated identity column for your table.
Actually no, it hasnt worked yet. On a simple level, I should just be able to load an initial file of data to the table, then load additional files, which update the existing records in the table, and add new ones from the file, should they not already exist. Now, if I do a full DELETE, then BULK INSERT, that works fine - no issues at all. its the BULK UPSERT where this issue comes in. Perhaps I'll try creating a different table with a new dataset, and see what happens.
Hmmm, generated identity column could be an option - are there any restrictions to doing that on a Retool Hosted Postgres DB? (as this one is)
Sorry about the late reply here @dhretool!
As far as I know, there shouldn't be any limitations here. You should be able to, for instance, copy the examples directly from the doc linked above:
Is there something in particular you're thinking of?
Hello,
i'm facing the same issue and not able to overpass it with BULK UPSERT. Any news regarding this point?
Hey @Francois_Tremblay!
I think things got a bit muddled here and we didn't come to a clear solution. Would you mind posting a screenshot of the query you're using with the error you're seeing to confirm the issue?
Screenshots of your table column settings might help as well as would the result of a schema query on that table!
Hi, I am seeing the same issue on a SQL database table with autogenerating primary key. Is there a fix for this yet? Many thanks!
Hey @pennyb!
Would you mind sharing a screenshot of the error you're getting along with the query itself? Additionally, any schema information you can share about your table would be appreciated! Lastly, it would be helpful to know what kind of SQL database it is?
I think I'm running into the same problem, I tried to set primary key to null or "" and got below error
I'm using PGsql @ supabase, primary key "id" is not-nullable
this is the upsert query config
@rxunique thanks for surfacing. At a high level, I'm thinking: Retool does allow the primary key to be null or "". It's a totally different thing if your underlying database has a constraint that primary keys can't be null or empty (which is quite common). That's what it sounds like could be happening here:
What you're seeing is an error from your database, that you're trying to insert a row with a null primary key. It's coming from your database and isn't a Retool-specific issue.
Still, to make sure I'm understanding your example correctly, what is the value of {{diary_id}}? Do you expect that that is an actual value, but Retool is incorrectly passing a null value? Or do you expect it to evaluate to null, leading to the errors from your db since it can't accept null ids?
Hi, you are kinda right but also not at the same time. let me explain.
You are correct that id column has not null condition in supabase, and it is auto increment.
With that I learned in last few days, I'd assume the upsert action actually is ON CONFLICT () under the hood which would explain why error with {{diary_id}} = null
I also learned there are other ways to implement upsert in the case of auto id and no other unique field
In my app, there is a single form handling both update and create, and it differentiate by if {{diary_id}} is null, so a few days ago I was assuming the filter condition will be ignored when {{diary_id}} doesn't exist
Whether is this a retool-specific issue is depending on perspective
- For experienced devs, they'd be expected to know about sql, most likely won't even want to use GUI for queries
- For non devs trying to slap an internal tool quickly, well, documentation would be nice
I'm having the same issue: (MSSQL)
The idea of this config is that if the table below the form doesn't have anything selected, it will insert a new record.
if there is something selected, it will update.
Here is my setup, notice the null in the selected ID
it chose insert as it should have, but then tried to insert the ID even though it isn't even part of the key value pairs.
@rxunique @Shegs thanks for the updates and thanks for your patience here. It looks like there might be an internal known issue with upsert in Retool on tables created with 'GENERATED ALWAYS' column type.
@rxunique Can you confirm that your table is set to GENERATE ALWAYS for the primary key?
@Shegs for MSSQL, would also be interested to get a confirmation if your table is set with the GENERATED ALWAYS setting or similar 
As a workaround, would it potentially work to try splitting into two queries, one that inserts and one that upserts? Either way, knowing about your db/table settings will help us narrow down what's going on. Thanks!
That's a new concept to me, only started using SQL recently because of retool. But seems the case according to supabase
That's a bit cumbersome. I did get it to work, but went with bulk upsert. Which has an off-chance changing unintended rows, but code is much easier to change and maintain
Would you mind have a look at my other bug posts recently? I have been using retool for less than 4 weeks, only halfway building a 5-10 page mobile app, found half a dozen bugs...
it's a standard Identity field in MSSQL which does auto increment and generate on insert.
Thanks @Shegs @rxunique - your comments seem to confirm our hypothesis that our Upsert doesn't play well with "generated always". It seems we always send the null value regardless of whether it's in the changeset. When you try to insert, tries to insert the null as the filter and that's where it throws an error. And it seems like MSSQL 'IDENTITY' has the same behavior.
I've surfaced this internally for our eng team and will keep this thread updated with any new info.
3 Likes