CODEWITHSUNDEEP
javascriptsqlpostgresqlsql-deletepg-promise
A. L
A. L

Reputation: 12649

pg-promise quick way to delete set of tuples

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?

Upvotes: 2

Views: 1113

Answers (2)

vitaly-t
vitaly-t

Reputation: 25840

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.

Upvotes: 3

GMB
GMB

Reputation: 222482

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

Upvotes: 2

Related Questions