How to get inserted row IDs after batch insert?

I'm using Retool to manage data entry for a PostgreSQL DB. In particular, I have a form that allows me to add many products at once. The form also allows selecting a tag that would apply to all these products.

I've been struggling when trying to achieve the following:

  • Create rows (batch insert) for all products in the form upon submission
  • Tag those new products. Since there is a many-to-many relationship between products and tags, I need to do a second batch insert in the product_tag table.

My initial idea to solve it was to capture the ID of each newly created product (first query) and somehow run a second query –triggered by the success of the first one– in which a second batch insert would take place. However, I seem unable to access the set of newly created product ids from the first query.

Any ideas on how to overcome this?

Hi @ale! Welcome to the forum! :sunglasses:

Hmmm tough question!

One thought would be to update both the products and tags table in the same query. Using a Postgres insert statement, which returns the id value into another insert statement. A short example would be:

This would insert two users into the users table and two tags with user_id of the two new users into the tags table.

Thanks for the quick response, @ben! That worked out :slight_smile:

1 Like