Postgres Pro
Downloads
Home   >   mailing lists

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

From Ashwin Jayaprakash
Subject Update HSTORE record and then delete if it is now empty - What is the correct sql?
Date
Msg-id CAF9YjSA-wEdcbHitDw19jM6K=giGESiojJ08_coUcLmTHacj3g@mail.gmail.com
Whole thread Raw
Responses 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?
List pgsql-sql
Tree view
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 by date:

Previous
From: denero team
Date:
Subject: Re: need help
Next
From: Don Parris
Date:
Subject: Re: Summing & Grouping in a Hierarchical Structure