Update SQL n:n table with multiple associations

I have done this before, but before I mindlessly repeat something that I consider as a hack, I'm asking here.

I have 3 tables (details left out for clarity) :

inv_items

id bigserial (PK)
sku character varying(24)
name character varying(32)
...

inv_item_groups

id bigserial (PK)
name cahracter varying(32)
...

inv_item_group_members

item_group_id bigint (FK -> inv_item_groups)
item_id bigint (FK -> inv_items)

Now, in my code, I have an object like so (in pseudo-code)

class ItemGroup
   id:long
   groupName:String
   items:long[]

and these objects can be modified, then needs to be updated. Since I want to preserve the key integrity, I need the inv_item_group_members table (otherwise, I would've used other solutions).

Now, the usual way I was doing this was to

DELETE FROM inv_item_group_members WHERE item_group_id = $1
-- where $1 is the object's id

the, for each items in the object

INSERT INTO inv_item_group_members (item_group_id, item_id) VALUES ($1, $2)

Is there a better solution? What are the alternative? I'm thinking of a SQL function, but not really sure what's the best approach here (I'm not very experienced with PGSQL, yet.) I have read about writable CTE, but it does not address the case when elements are removed from the array (i.e. association removed).

My suggestion is that you only delete from inv_item_group_members the items you deleted from the object and only insert into it the new items you added to the object.

You can do that by creating an itemsToDelete array, which you populate with item_ids each time you call the removeItem method of the class and an itemsToInsert which you populate each time you add an item whith the addItem method.

Then when you call the save method you delete the items whose item_id are present in the itemsToDelete array (obviously restricted also to group_id). Then you proceed to insert items present in the itemsToInsert array.

To make it resilient, you can either use a Map instead of arrays in order to prevent duplicates, or catch and ignore PK exceptions during the insert.

StackExchange Database Administrators Q#68825, answer score: 2