Update HSTORE record and then delete if it is now empty - What is the correct sql?

From: Ashwin Jayaprakash <ashwin(dot)jayaprakash(at)gmail(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: Update HSTORE record and then delete if it is now empty - What is the correct sql?
Date: 2013-02-22 22:46:47
Message-ID: CAF9YjSA-wEdcbHitDw19jM6K=giGESiojJ08_coUcLmTHacj3g@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

Hi, here's what I'm trying to do:
- I have a table that has an HSTORE column
- I would like to delete some key-vals from it
- If after deleting key-vals, the HSTORE column is empty, I'd like to
delete the entire row

I have the sample SQL here and the DML I was trying out. I thought a CTE
query would be the best and I could be completely wrong.

*Problem:* The DELETE query seems unable to delete a row returned by the
WITH clause:

create table up_del(name varchar(256) primary key, data hstore);

insert into up_del(name, data) values
('aa', 'a=>123'),
('bb', 'b=>456, a=>456'),
('cc', 'c=>678'),
('dd', 'd=>901'),
('ee', '');

select * from up_del;

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

Thanks,
Ashwin.

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Don Parris 2013-02-23 00:15:12 Re: Summing & Grouping in a Hierarchical Structure
Previous Message denero team 2013-02-22 18:30:04 Re: need help