with update_qry as( update up_del as r set data = delete(data, 'c=>678') where name = 'cc' returning r.* ) delete from up_del where name in (select name from update_qry) and array_length(akeys(data), 1) is null;
Q1: That DELETE statement does not work but the rest of the query seems ok. Is this something to do with REPEATABLE READ or locked rows or something else?
The WITH clause seems to return the correct rows. A similar query with SELECT instead of DELETE seems to return the targeted rows:
with update_qry as( update up_del as r set data = delete(data, 'c=>678') where name = 'cc' or name = 'ee' returning r.* ) select *, array_length(akeys(data), 1) is null from update_qry;
Q2: What the best way to check if an HSTORE is empty? Is this it "array_length(akeys(data), 1) is null"?