Bulk insert SQL mode. Is it possible?

Non GUI bulk insert

Hello fellow retoolers.
Please consider the following scenario:

I have three tables in my PostgreSQL DB - 1. Lines, 2. Clients, 3. Subscriptions.
Each subscription is made of a relation between a line and a client.

When adding lines into the database there's also an option to automatically create a subscription for the inserted lines with the given client.

The problem? Wehn trying to create a subscription I need to know the line_id of the line that were just inserted and it's not possible with the GUI mode.
If I was using regular SQL query I would’ve done in like this:

WITH ins AS (
   INSERT INTO lines VALUES
      (default, phone_number, sim_number, ...),
      (default, phone_number, sim_number, ...),
      (default, phone_number, sim_number, ...),
      (default, phone_number, sim_number, ...)
   RETURNING id)
SELECT array_agg(id) INTO line_ids_list
FROM ins;

And then I would have another query that is in-charge of inserting the rest based on the returned ids.

But since for bulk update I have to use the GUI Mode, then I can't think of a way to pull this off inside of retool.

Any help would be greatly appreciated !

You would need to run a trigger in the DB itself AFTER the insert happens.....to get that ID

Thank you for your help.

You would need to run a trigger in the DB itself AFTER the insert happens.....to get that ID

I didn't want to go the extra mile, and have another table just to be able to perform a small part of the application.

I guess that's the only option though as Retool will not let me add any SQL to the bulk insert query.

1 Like

@Mendy

Hey there :wave: For a bulk update in SQL mode, one option could be to write a JS query to trigger a single row update for each record in an array by following a setup similar to this example from our docs. You can create a single insert query and loop through the array of values you'd like to insert in a JS query, passing each one to the query as additionalScope. Hope that helps!