pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedUpdate HSTORE record and then delete if it is now empty - What is the correct sql?
5+ messages / 3 participants
[nested] [flat]
* Update HSTORE record and then delete if it is now empty - What is the correct sql?
@ 2013-02-22 22:46 Ashwin Jayaprakash <ashwin.jayaprakash@gmail.com>
0 siblings, 2 replies; 5+ messages in thread
From: Ashwin Jayaprakash @ 2013-02-22 22:46 UTC (permalink / raw)
To: 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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Update HSTORE record and then delete if it is now empty - What is the correct sql?
@ 2013-02-23 05:53 Ian Lawrence Barwick <barwick@gmail.com>
parent: Ashwin Jayaprakash <ashwin.jayaprakash@gmail.com>
1 sibling, 1 reply; 5+ messages in thread
From: Ian Lawrence Barwick @ 2013-02-23 05:53 UTC (permalink / raw)
To: Ashwin Jayaprakash <ashwin.jayaprakash@gmail.com>; +Cc: pgsql-sql
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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Update HSTORE record and then delete if it is now empty - What is the correct sql?
@ 2013-02-23 10:49 Tom Lane <tgl@sss.pgh.pa.us>
parent: Ashwin Jayaprakash <ashwin.jayaprakash@gmail.com>
1 sibling, 1 reply; 5+ messages in thread
From: Tom Lane @ 2013-02-23 10:49 UTC (permalink / raw)
To: Ashwin Jayaprakash <ashwin.jayaprakash@gmail.com>; +Cc: pgsql-sql
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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Update HSTORE record and then delete if it is now empty - What is the correct sql?
@ 2013-02-23 18:04 Ashwin Jayaprakash <ashwin.jayaprakash@gmail.com>
parent: Ian Lawrence Barwick <barwick@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: Ashwin Jayaprakash @ 2013-02-23 18:04 UTC (permalink / raw)
To: Ian Lawrence Barwick <barwick@gmail.com>; +Cc: pgsql-sql
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>:
> (...)
> >
> > 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
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Update HSTORE record and then delete if it is now empty - What is the correct sql?
@ 2013-02-23 18:07 Ashwin Jayaprakash <ashwin.jayaprakash@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 5+ messages in thread
From: Ashwin Jayaprakash @ 2013-02-23 18:07 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
Thanks Tom. I'll try it out soon.
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2013-02-23 18:07 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-02-22 22:46 Update HSTORE record and then delete if it is now empty - What is the correct sql? Ashwin Jayaprakash <ashwin.jayaprakash@gmail.com>
2013-02-23 05:53 ` Ian Lawrence Barwick <barwick@gmail.com>
2013-02-23 18:04 ` Ashwin Jayaprakash <ashwin.jayaprakash@gmail.com>
2013-02-23 10:49 ` Tom Lane <tgl@sss.pgh.pa.us>
2013-02-23 18:07 ` Ashwin Jayaprakash <ashwin.jayaprakash@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox