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
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
