Does PostgreSQL provide a way to return the number of remaining rows satisfying a condition after running a DELETE query in one query?

user_6095754 0
Viewed 189

DELETE returns the number of rows deleted as well as providing a way to act on the deleted data in the RETURNING clause. I'm wondering if there is a way to get the number of remaining rows satisfying a WHERE condition without running a second query, or if the best practice here is simply to make a second query.

postgresql sql
2 Answers

Your question is rather strange.

If the DELETE command wasn't interrupted, then the number of remaining rows would always be zero.

I mean, if you run

DELETE FROM SomeTable
WHERE <SomeCondition>

and this command was successfully committed, then if you try to count the remaining rows with the same condition:

SELECT COUNT(*)
FROM SomeTable
WHERE <SomeCondition>

that would always return 0. (Obviously, if there are no concurrent processes that change the table values.)

If the DELETE command was interrupted, then it would roll back, then none of the rows would be deleted.

Any DELETE or UPDATE or INSERT command is atomic, either all of it is done, or none of it is done in RDBMS like Postgres.

That's why Postgres does not provide a way to return the number of remaining rows after DELETE - it is always 0.

If there are other concurrent processes that may change the table while the original DELETE runs, and they change the table in such a way that the original DELETE doesn't "see" these changes and doesn't delete them, then you should explicitly run the second SELECT COUNT(*) to get the latest count.

user_41160170

You have to call two SQL commands. But you must do it in a single transaction. If you don't call both commands in a single transaction the second command (the select) may bring you information that not corresponds to the delete operation.

If you write:

    await client.query(`DELETE FROM table1 WHERE sub_kind='A1'`);
    var remaindCount = await client.query(`SELECT count(*) FROM table1 WHERE kind='A'`);

It may happen that between the two commands another process executes another SQL command that influences the result.

But if you write:

    try{
        await client.query(`BEGIN TRANSACTION`);
        await client.query(`DELETE FROM table1 WHERE sub_kind='A1'`);
        var remaindCount = await client.query(`SELECT count(*) FROM table1 WHERE kind='A'`);
        await client.query(`COMMIT`);
    }catch(err){
        await client.query('ROLLBACK');
        throw err;
    }

A command executed between DELETE and SELECT commands does not affect the result. That ocurrs because PostgreSQL is an ACID database.

user_52032710
Related