Postgres: Select multiple columns and compare with array

Question

I would like to do the following (written in some kind of simplified pseudo code) in just one PL/PGSQL function inside a Postgres 12 database:

SELECT numcolA, numcolB FROM myitemtable INTO _unused_items_array;

FOR EACH _numcol1, _numcol2 IN SELECT num_col1, num_col2 FROM some_items_table LOOP

    IF CONTAINS(_unused_items_array, [_numcol1, _numcol2]) THEN
        REMOVE_ARRAY_ITEM_IF_EXISTS(_unused_items_array, [_numcol1, _numcol2]);
        -- following lines containing REMOVE_ARRAY_ITEM_IF_EXISTS are simplified
        -- they will be in real life much more difficult to calculate
        REMOVE_ARRAY_ITEM_IF_EXISTS(_unused_items_array, [_numcol1 - 1, _numcol2 - 1]);
        REMOVE_ARRAY_ITEM_IF_EXISTS(_unused_items_array, [_numcol1 + 3, _numcol2 + 3]);
    END IF;

END LOOP

SELECT numcolA, numcolB FROM myitemtable INTO _used_items_array;
FOR EACH _unused_item IN _unused_items_array
    REMOVE_ARRAY_ITEM_IF_EXISTS(_used_items_array, _unused_item);

It can not be done just in SQL because the calculation (comment above) is much more complex than written here.

So, does anybody have an idea how I can solve it with PL/PGSQL – because the array functions in PL/PGSQL are driving me crazy in solving this (in C# it would be done in a second - but no option).

Addition (edit) As requested I add some sample data to explain the requirement:

myitemtable contains (amongst others) the following columns:

+ -------------- + -------------- +
| numcolA        | numcolB        |
+ -------------- + -------------- +
| 1              | 1              |
+ -------------- + -------------- +
| 1              | 2              |
+ -------------- + -------------- +
| 2              | 5              |
+ -------------- + -------------- +
| 2              | 9              |
+ -------------- + -------------- +

some_items_table contains (amongst others) the following columns:

+ -------------- + -------------- +
| num_col1       | num_col2       |
+ -------------- + -------------- +
| 1              | 2              |
+ -------------- + -------------- +
| 2              | 9              |
+ -------------- + -------------- +

Within the if condition there will be some other items 'virtually calculated', lets say (1|1), (1|3), (2|2), (2|8), (2|10), (3|9) and it will be checked whether these items are in _unused_items_array (and will be removed if they are - and some are, as you can see).

This all will lead at the end to an _used_items_array which will contain only item (2|5) in this example.

More often than not using arrays is a sign of a questionable database design. Properly normalized models usually don't require this. Maybe if you take a step back and explain us the underlying problem you are trying to solve (by providing sample data and expected results) we could come up with set base, relational solution that doesn't require the use of arrays to begin with — user330315
– user330315, Commented Nov 10, 2020 at 11:42
Does [_numcol1 + 3, _numcol2 + 3] indicate the indexes of the elements in the array that should be be removed or the values to be removed? What data type are the array elements? — user330315
– user330315, Commented Nov 10, 2020 at 11:48
Thanks for your reply. I understand your remark about the db design, but be sure: Not the case over here. The requirement of the REMOVE_ARRAY_ITEM_IF_EXISTS is that the element will be removed. — DevNewBee
– DevNewBee, Commented Nov 10, 2020 at 14:38
I've edited the description of the problem and added (as requested) some sample data and expected result. — DevNewBee
– DevNewBee, Commented Nov 10, 2020 at 14:50

Erwin Brandstetter · Accepted Answer · 2020-11-14 06:14:24Z

1

There are many ways to do this, a plain SQL DELETE being possibly the "simplest":

DELETE FROM myitemtable m
USING  some_items_table s
WHERE (m.numcolA , m.numcolB) IN (
      (s.num_col1, s.num_col2)  -- row values from some_items_table
    , (1,1), (1,3), (2,2), (2,8), (2,10), (3,9) -- "virtually calculated" row values
      );

db<>fiddle here

This is comparing ROW values. Don't forget that NULL values don't compare equal in such a test. Related:

How do I (or can I) SELECT DISTINCT on multiple columns?

You can nest the statement in a PL/pgSQL function if required.

Involving arrays doesn't seem necessary. XY problem? It's simple enough to achieve the same with arrays, too, though.

edited Nov 14, 2020 at 6:14

answered Nov 14, 2020 at 6:06

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

jian Over a year ago

Allow me, How come (2,5) got deleted? seem like I vote your answer, you can not grow score. I guess I voted too many of your answers.

Erwin Brandstetter Over a year ago

@Mark (2,5) is the only surviving row in the example. About reputation see: meta.stackexchange.com/a/7238/169168

jian Over a year ago

I am sorry. reading too much of your fine answers..... brain hurts now, i guess.

Collectives™ on Stack Overflow

Postgres: Select multiple columns and compare with array

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related