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

Поиск
Список
Период
Сортировка
От Ashwin Jayaprakash
Тема Update HSTORE record and then delete if it is now empty - What is the correct sql?
Дата
Msg-id CAF9YjSA-wEdcbHitDw19jM6K=giGESiojJ08_coUcLmTHacj3g@mail.gmail.com
обсуждение исходный текст
Ответы Re: Update HSTORE record and then delete if it is now empty - What is the correct sql?
Re: Update HSTORE record and then delete if it is now empty - What is the correct sql?
Список 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.

В списке pgsql-sql по дате отправления: