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