PostgreSQL – Update n:n Table with Multiple Associations

postgresql

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).

Best Answer

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.

Major points

Use the simpler ST_MakePoint to construct a point geometry from numeric input.
You do not need ST_AsText() at all, since your column geom is of type geometry already.
Fastest and simplest way to get the geometry for the biggest version per polyname in table unknown_table is to use DISTINCT ON, which is a Postgres extension of the SQL standard. More details in this related answer:
How do I efficiently get "the most recent corresponding row"?
In reference to the currently accepted but invalid answer: the concatenation operator in Postgres (and standard SQL) is || (not +)

PostgreSQL – SQL Update Multiple Columns Matching a Pattern

I think you're trying to avoid writing:

  UPDATE mytable SET
   door_open = door_open + INTERVAL '1' HOUR,
   door_close = door_close + INTERVAL '1' HOUR,
   car_open = car_open + INTERVAL '1' HOUR,
   car_close = car_close + INTERVAL '1' HOUR,
   ... blah blah ...
  WHERE
   ...

and instead want a way to match column names by wildcard.

If that is the case, then no, there is no built-in way to do that.

You can construct a query dynamically using PL/PgSQL: Use a query against the information_schema.columns view to get column names, then string concatenation to form the query. Then you can run it with EXECUTE. There are many examples of such dynamic SQL elsewhere on Stack Overflow.

e.g.

DO
$$
DECLARE
  sqlstring text;
BEGIN
  SELECT INTO sqlstring
   'UPDATE blah SET '
   || string_agg(format('%I = %I + INTERVAL ''1'' HOUR', column_name, column_name), ', ') 
   || ' WHERE true'
  FROM information_schema.columns
  WHERE table_name = 'blah'
    AND table_schema = 'public';

  EXECUTE sqlstring;
END;
$$;

inv_items

inv_item_groups

inv_item_group_members

Best Answer

Related Solutions

PostgreSQL – Update Table with Data from Another Table

Major points

PostgreSQL – SQL Update Multiple Columns Matching a Pattern

Related Question