returning a value from postgresql delete that impacts 0 rows-CodePudding

is it possible to return a value from a delete statement in postgres when no rows were deleted?

Example:

-- Create a table
create table dummy(id int);

delete from dummy where id = 999 returning <ANSWER HERE>
-- Desired output = "1"

What I've tried:

delete from dummy where id = 999 returning 1
-- returns: null

CodePudding user response：

The principle of using RETURNING after a DELETE command is that - RETURNING gets only deleted records. If the RETURNING will get records on the not deleted process, then this is will be an illogical or abnormal situation.

But, you can use a logical process for doing this. For example, using the UNION command we can add one record to the result of RETURNING. So, when records are deleted we are getting deleted records one record of UNION. When records are not be deleted we get only one record of the UNION command.

Example query for about this process:

with del_table as 
(
    delete from test.test 
    where id = 4100 
    returning id, title
)
select id, title from del_table
union all 
select 100, 'test';

Result:
id      title
----------------
4100    TestData
100     test