deleting and returning data in PostgreSQL

Question

I have the following problem: my table is big enought (millions of data rows), this is temporary data which I process. I need to select max and min of one column accrding to some criteria, process this information and remove data according to the same criteria. Actually, the simplest implementation looks like:

select max(col), min(col) from _TABLE_ where _CONDITION_;
...
delete from _TABLE_ where _CONDITION_;

table is big, and when I process it, quering that way in cycle, it takes some time. I thought I can optimize it using "returning" in delete, like

delete from _TABLE_ where _CONDITION_ returning max(col), min(col);

it would be absolutely what I need, but... it does not work at all :) talking that I can't use aggregate functions in returning clause...

is there any good way to improve two queries (selecting max/min of data and deleting the same data) making one query instead? Any trick?

thank you in advance for any information, Maxym

Are the CONDITIONS mutually exclusive and relatively low in number? — cope360
– cope360, Commented Feb 9, 2010 at 19:53
sorry, what do you mean? Actually "col".. hm, in reality I have two columns, which represent coordinate of point (latitude and longitude), so I select delete all points from table according to some rectangle, but I have to know what was the min/max longitude and longitude of deleted points (real, because approx I could take rectangle :) — Maxym
– Maxym, Commented Feb 14, 2010 at 1:44

Gismo Ranas · Accepted Answer · 2012-05-18 15:44:16Z

8

You can do:

with foo as(delete from table where _CONDITION_ returning col)
select max(col), min(col) from foo

edited May 18, 2012 at 15:44

answered May 9, 2012 at 9:05

Gismo Ranas

6,5724 gold badges30 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Maxym Over a year ago

why only from 9.1? the idea is to use CTE function, right? it was enabled for instance in 8.4 too.

Gismo Ranas Over a year ago

yes, you are right. I think you cannot do WITH (...) DELETE/INSERT before 9.1 : postgresql.org/docs/9.1/static/queries-with.html . I just mixed things up, thanks!

Tometzky · Accepted Answer · 2010-02-09 13:30:27Z

2

Use a function like this:

create temporary table test (value int);
insert into test select generate_series(1,100);

create or replace function delete_even_from_test_and_return_min_deleted()
  returns int as
$$
declare
  _value record;
  min int;
begin
  min=X'7FFFFFFF'; -- INT_MAX
  for _value in
    delete from test where value%2=0 returning value
  loop
    if min>_value.value then
      min=_value.value;
    end if;
  end loop;
  return min;
end;
$$ language plpgsql;

select count(*) from test;
100

select delete_even_from_test_and_return_min_deleted();
2

select count(*) from test;
50

edited Feb 9, 2010 at 13:30

answered Feb 9, 2010 at 13:09

Tometzky

24.2k5 gold badges64 silver badges79 bronze badges

Collectives™ on Stack Overflow

deleting and returning data in PostgreSQL

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related