I have an App consisting of a connection to a Postgres DB, from which I extract one specific table through a SELECT * in order to show the data in a Retool table called EventTable.
Int & Bigint columns get coerced to strings, as you can see in the screenshot.
All columns can be modified. I have added a connection "GUI mode -> Run query only when manually triggered" with action type "Bulk update via a primary key" and the primary key column is set to "id", i.e. the PK in the table.
Whatever column I modify in the table, be it a string column or a int/bigint column, I run into the following error:
Query returned an error
Query ran successfully
message:"Unexpected error occurred while running query"
queryExecutionMetadata:{} 4 keys
estimatedResponseSizeBytes:0
resourceTimeTakenMs:1199
isPreview:false
resourceType:"postgresql"
source:"resource"
I have tried different configurations of the array of records to update, from the default one:
{{EventTable.recordUpdates }}
to one that parses the columns back from strings to int:
I don't think so. I'm not coercing anything, at least not on purpose. In the lateral view, I'm keeping the columns on Column type = Auto or eventually their actual type (text number etc) as per Postgres types. Anyway, I doesn't seem that changing column type there has any influence on the actual column types, besides on how they're printed in the app.
Good: it doesn't seem to be a column type problem.
I used a transformer to ingest the quary output, set correctly the int columns and then pass the transformer output to the Retool table.
In your latest screenshot, it looks like you're still passing in the table.recordUpdates property instead of your transformer.value, is this intentional?
What column types does your db table expect? Does the query run if you pass in {{ ListEvents_.value}} to your update query?
It is intentional. ListEvents_.values feeds the table, not the UpdateEvents. When table rows are modified, I use EventTable.recordUpdate to identify the records that have been changed and update them in the Postgres DB. I expect the recordUpdate to have a format reflecting those of the table (and it looks it is so).
I have now tried to add a new transformer which does the following:
let data = {{EventTable.recordUpdates}};
data = data.map(row => _.pick(row, ["id","organizer"]));
return data
so that I only select two columns, and I know that in the Postgres DB they should be an int8 and a text, and in the retool table seems they are so. Then I pass {{UpdateEvents_.value}} to the UpdateEvents:
Thank you for clarifying that! Asking my team to see if they've seen any similar issues. OOC, does it give you the same error if you hardcode an array to update?
How strange! I’ve been able to get this to work on my end. Are you able to try updating the same data from another source (separate from Retool)? Are there any table validation rules on the postgres side that you can think of?
And if you run this query, what are the column types?
SELECT
table_name,
column_name,
data_type
FROM
information_schema.columns
WHERE
table_name = 'your_table_name’
Via DBeaver everything works fine. Before opening this thread, I also removed most of the constraints on the table, in order to make the case as simple as possible. I only left the ID as a Primary Key. So the only real constraint is specifying the ID when creating a new row (I removed the auto insert via sequence). But in this thread we're trying to do an UPDATE, so it shouldn't even be an issue. (Btw, even the INSERT doesn't work).
See below both an INSERT INTO table, an UPDATE table, and the table schema.
Finally, may be useful to know that I'm using the same user and the same connection settings on DBeaver and Retool. Thus, shouldn't be an issue of authentication or similar.
Thank you for doing all that digging. If you try a single update in Retool, do you get the same error? And as a last ditch troubleshooting effort, could you try recreating your resource and try creating a query with that new resource? I'll also keep digging on my end to see if there's anything else we can try.
I had to move forward with some workaround, given that time was pressing. So it took me a while to find time to do more tests. But here we are, and here's what I found.
Doing a single UPDATE query, directly via 'SQL Mode', results in success, independently from what data type we use. Here you can see two screenshots with different data types on the id column (int8 in the DB), I was doublechecking directly on the DB if they were working, and they are.
Doing those queries from DBeaver, gives the same exact result.
Setting up a new separate DB, always PostgresQL, with the same tables and the same DDLs, "solved" the problem. I mean that doing via GUI Mode a Bulk Update/Upsert/Insert using the new database, it didn't result in the error.
In short, it seems that only in GUI Mode it gives this error, and on this database. I have opened a ticket also with the DB providers.
Currently, as a workaround, I'm using the 2nd DB. But I'm also thinking of manually creating SQL Update queries through string manipulation and pass them to Retool in 'SQL Mode'.
I remain hopeful of finding a solution as fast as possible.
It sounds like there’s something wrong with the 1st DB connection Let me know if you hear anything back from your DB providers.
I have seen Retool resources very occasionally get corrupted, and the only fix is to re-create a new resource with the same exact credentials. It’s a bit of a pain to reconnect all quarries to this new duplicate resource, but it’s better than nothing if the original resource is indeed corrected.
I’ve been able to get this to work on my end. Are you able to try updating the same data from another source (separate from Retool)? Are there any table validation rules on the postgres side that you can think of?
