pg-promise quick way to delete set of tuples

user_6554121 0
Viewed 472

Is there a better way to write the following:

DELETE FROM
    table
WHERE
    _id = $<id>
    AND (
        item_id = $<item_id>
        AND map_id = $<map_id>
        OR
        item_id = $<another_id>
        AND map_id = $<another_map_id>
        ...
    )

The dataset format I have is:

[
    {
        item_id: "some_id",
        map_id: "other_id"
    }
    ...
]

What would be a good way to write this in pg-promise, or even just plain postgreSQL?

pg-promise sql-delete postgresql sql javascript
2 Answers
DELETE FROM table
WHERE
    _id = $<id>
    AND (item_id, map_id) IN ($<values:raw>)

If you have the following as your input data:

const input = [
    {
        item_id: 'some_id',
        map_id: 'other_id'
    }
    ...
];

then you can generate values with pg-promise as follows (see helpers.values):

const values = pgp.helpers.values(input, ['item_id', 'map_id']);

or you can pass columns as a full ColumnSet, if more details are needed, like type casting, etc.

user_11020510

Postgres supports tuple equality, so you could write this as:

DELETE FROM table
WHERE
    _id = $<id>
    AND (item_id, map_id) IN ( 
        ($<item_id>, $<map_id>),
        ($<another_id>, $<another_map_id>),
        ...
    )
user_106767160
Related