Postgres merge two rows with common array elements

Question

I have a postgres table with a column names "ids".

+----+--------------+
| id |     ids      |
+----+--------------+
|  1 | {1, 2, 3}    |
|  2 | {2, 7, 10}   |
|  3 | {14, 11, 1}  |
|  4 | {12, 13}     |
|  5 | {15, 16, 12} |
+----+--------------+

I want to merge rows with at least one common array element and create a new row from that (or merge into one existing row). So finally the table would look like the following:

+----+--------------------------+
| id |           ids            |
+----+--------------------------+
|  6 | {1, 2, 3, 7, 10, 14, 11} |
|  7 | {12, 13, 15, 16}         |
+----+--------------------------+

Order of array elements in the resulting table does not really matter but they must be unique.

The rows are added independently from another system. For example we could add a new row where ids are {16, 18, 1}.

Right now to make sure we combine all the rows with at least one common array element, I am doing the calculations in my server (Node.js).

So before I create a new row, I pull all the existing rows in the database that have at least one item in common using:

await t.any('SELECT * FROM arraytable WHERE $1 && ids', [16, 18, 1])

This gives me all the rows that have at least 16, 18 or 1. Then I merge the rows with [16, 18, 1] and remove duplicates.

With the availability of this new array, I delete all existing rows fetched above and insert this new row to the database. As you can see, most of the work is being done in Node.js.

Instead of this I am trying to create a trigger, which will do all these steps for me as soon as I add the new row. How do I go about doing this with a trigger. Also, are there better ways?

pewpewlasers · Accepted Answer · 2021-01-11 10:20:22Z

Can procedure suffice?

CREATE OR REPLACE PROCEDURE add_ids(new_ids INT[])
AS $$
    DECLARE sum_array INT[];
    BEGIN
        SELECT ARRAY (SELECT UNNEST(ids) FROM table1 WHERE table1.ids && new_ids) INTO sum_array;
        sum_array := sum_array || new_ids;
        SELECT ARRAY(SELECT DISTINCT UNNEST(sum_array)) INTO sum_array;
        DELETE FROM table1 WHERE table1.ids && sum_array;
        INSERT INTO table1(ids) SELECT sum_array;
    END;
$$
LANGUAGE plpgsql;

Unfortunately inserting row inside trigger calls another trigger causing infinitie loop. I do not know work around that.

PS. Sorry if creating another answer is bad practice. I want to leave it for now for reference. I will delete it when the problem is resolved.

Edit by pewpewlasers:

To prevent the loop another table is probably needed. I have created a new temporary table2. New arrays can be added to this table. This table will have a trigger which does the calculations and saves it to table1. It also deletes this temporarily created row.

CREATE OR REPLACE FUNCTION  on_insert_temp() RETURNS TRIGGER AS $f$
DECLARE sum_array BIGINT[];
BEGIN
    SELECT ARRAY (SELECT UNNEST(ids) FROM table1 WHERE table1.ids && NEW.ids) INTO sum_array;
    sum_array := sum_array || NEW.ids;
    SELECT ARRAY(SELECT DISTINCT UNNEST(sum_array)) INTO sum_array;
    DELETE FROM table1 WHERE table1.ids && sum_array;
    INSERT INTO table1(ids) SELECT sum_array;
    DELETE FROM table2 WHERE id = NEW.id;
    RETURN OLD;
END
$f$ LANGUAGE plpgsql;

CREATE TRIGGER on_insert_temp AFTER INSERT ON table2 FOR EACH ROW EXECUTE PROCEDURE  on_insert_temp();

Thanks a lot Robert, using your two answers I have created an edit that solves my issue. Thanks again!

Robert Żyłan · Accepted Answer · 2021-01-10 21:02:35Z

1

Given tables

CREATE TABLE table1(id serial, ids INT [] )
CREATE TABLE table2(id serial, ids INT [] )

the trigger can looks like that


CREATE OR REPLACE FUNCTION sum_tables_trigger() RETURNS TRIGGER AS $table1$
   BEGIN
      INSERT INTO table2(ids) SELECT ARRAY(SELECT DISTINCT UNNEST(table1.ids || new.ids) ORDER BY 1) FROM table1 WHERE table1.ids && new.ids;
      RETURN NEW;
   END;
$table1$ LANGUAGE plpgsql;

CREATE TRIGGER sum_tables_trigger_ BEFORE INSERT ON table1
FOR EACH ROW EXECUTE PROCEDURE sum_tables_trigger();

tableA.ids && tableB.ids returns true, if tables have common element.

tableA.ids || tableB.ids adds elements.

ARRAY(SELECT DISTINCT UNNEST(table1.ids || new.ids) ORDER BY 1) removes duplicates.

answered Jan 10, 2021 at 21:02

Robert Żyłan

964 bronze badges

1 Comment

pewpewlasers Over a year ago

What if there is no table 1? I will revise the question to avoid confusion.

Collectives™ on Stack Overflow

Postgres merge two rows with common array elements

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related