I try to let users upload a csv and then insert the data in the connected postgresql-database. I have the problem that when inserting new data into one table new primary keys are generated in the database that I need for inserting into another table as foreign keys as the tables are connected. How can I achieve this?
I tried the following:
with "new_hl" as (
insert into "HL" ("HL")
values ({{formatDataAsObject(getBulkFromTable.data).HL}})
returning *
)
// ... insert in the other table using the IDs from "new_hl"
This successfully inserts data into my database table, but it inserts all entries of the HL-array as a single entry. However, I want to create a new row for each entry. How can I accomplish this? I attempted to use the map() function but couldn't get the result I want. Is it really necessary for me to write individual insert queries for each entry, as suggested by other answers to related questions in this forum?
You could call the function in Retool but the function would live in the db environment...
However, there may be other ways around this but I need more info...
What does the insert statement look like? Additionally, am I correct in assuming that you insert all data into HL table and then when complete you want to take each row and insert it into another table? You can do something like this in another query after initially inserting into HL using an Event handler to call the next query:
WITH inserted_data_config AS (
SELECT all, of, the fields, you want from the newly inserted data, in , HL
FROM HL
WHERE
ORDER BY TIMESTAMP DESC
LIMIT 1
) INSERT INTO your_other_table ( all, of, the fields, you want from the newly inserted data, in , HL)
I am just spitballing here because I don't know your set up.
I have the table "HL" for suppliers where I have a string-column for the name of the supplier and a serial primary key. Then I have another table that connects some tables and therefore has multiple columns where one is the supplier ID that references the primary key of the HL-table as a foreign key.
When entering the new supplier to the HL-table I also want the newly created ID to be written into the new row in the second table (together with other IDs according to the same pattern).
I hope this makes sense.
If I am to use event handlers, I don't know how to find out which new IDs have been assigned to the newly added suppliers after inserting them, except by an additional select-query.
Maybe, now that I think about it, using such an additional query is a solution, but I was hoping for a solution with a single insert-query.
Just want to jump in here and say that adding a second select query seems like a good way to go! It's also possible to build an INSERT statement that uses the RETURNING keyword to get the id back:
This requires SQL mode instead of GUI mode which means some additional coding but it could save you a query.
thanks for the answer. I tried to use the Returning-Keyword, but I had the issue that I was not able to insert the data correctly in SQL-mode. I only got it to write all entries of my array as one single entry in the database but I needed a single row for each entry.
I now got it to work using individual gui-insert and select queries
