Обсуждение: Update HSTORE record and then delete if it is now empty - What is the correct sql?
Update HSTORE record and then delete if it is now empty - What is the correct sql?
От
Ashwin Jayaprakash
Дата:
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 a table that has an HSTORE column
- I would like to delete some key-vals from it
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;
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;
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;
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;
Thanks,
Ashwin.
Re: Update HSTORE record and then delete if it is now empty - What is the correct sql?
От
Ian Lawrence Barwick
Дата:
2013/2/23 Ashwin Jayaprakash <ashwin.jayaprakash@gmail.com>: (...) > > Q2: What the best way to check if an HSTORE is empty? Is this it > "array_length(akeys(data), 1) is null"? Just a quick answer to your second question: I suspect it might be more efficient to check your HSTORE column against an empty HSTORE, e.g. WHERE val = ''::HSTORE HTH Ian Barwick
Ashwin Jayaprakash <ashwin.jayaprakash@gmail.com> writes: > 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 > 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 Nope, it won't, because a single query can only update any particular table row once, and the DELETE plus its WITH clauses is still only a single query. If you want "no empty hstore values" to be an invariant of your data structure, then expecting every update query to implement that correctly seems like a pretty bad idea anyway. Consider using a trigger to do that, ie something like BEFORE UPDATE FOR EACH ROW DO "if new hstore value is null then delete the row and return null". A problem with that approach is that the returned count of updated rows won't be very meaningful, and RETURNING values likewise. If that's a problem for you, you could use an AFTER trigger instead, which will be a little slower but it hides the deletes behind the scenes. (Note: a DELETE issued in a trigger is a separate query, which is why it doesn't fall foul of the limitation your WITH query did.) regards, tom lane
Re: Update HSTORE record and then delete if it is now empty - What is the correct sql?
От
Ashwin Jayaprakash
Дата:
Thanks, that makes sense.
On Fri, Feb 22, 2013 at 9:53 PM, Ian Lawrence Barwick <barwick@gmail.com> wrote:
2013/2/23 Ashwin Jayaprakash <ashwin.jayaprakash@gmail.com>:
(...)>Just a quick answer to your second question: I suspect it might be more
> Q2: What the best way to check if an HSTORE is empty? Is this it
> "array_length(akeys(data), 1) is null"?
efficient to check your HSTORE column against an empty HSTORE, e.g.
WHERE val = ''::HSTORE
HTH
Ian Barwick
Re: Update HSTORE record and then delete if it is now empty - What is the correct sql?
От
Ashwin Jayaprakash
Дата:
<div dir="ltr">Thanks Tom. I'll try it out soon.<br /></div>