[postgis-users] DELETE old lines in a table

Daniel Grum daniel.grum at unibw.de
Wed Aug 26 08:03:13 PDT 2009

P Kishor schrieb:
> 2009/8/26 Daniel Grum <daniel.grum at unibw.de>:
>> Hi all,
>> I've a problem with deleting old infos in a table.
>> I want put information into a table via "insert into" and delte the old
>> information that was saved in the tabele befor.
>> In adition to the "gid" column I have a column, called
>>  "zeitstempel"(timestamp), that shows me what info is old and what is the
>> latest infos.
>> Here is the sql:
>> INSERT INTO abbauholz
>> SELECT nextval('serial') AS GID,
>>      pt.name AS name,
>>      ST_Intersection(poly.the_geom,ST_Expand(pt.the_geom, 2000)) AS
>> the_geom,
>>      Area(ST_Intersection(poly.the_geom,ST_Expand(pt.the_geom, 2000))) AS
>> flaeche,
>>      Sum(Area(ST_Intersection(poly.the_geom,ST_Expand(pt.the_geom, 2000))))
>> AS sum_flaeche,
>>      CURRENT_TIMESTAMP AS zeitstempel
>> FROM public.wald_by poly, public.holzfaeller pt
>> WHERE poly.the_geom && ST_Expand(pt.the_geom, 2000)
>> GROUP BY pt.name, poly.the_geom, pt.the_geom;
>> DELETE FROM abbauholz WHERE date_part('second',zeitstempel) <
>> date_part('second', CURRENT_TIMESTAMP);
>> the last line is the most important, because here came DELETE order.
>> But it doesn't work, the old info(old lines) stay in the table after the sql
>> order.
>> Can someone help me?
> Maybe you need a more hires timer than seconds. Instead of depending
> on time, why not just delete everything in the table and then insert
> new rows? Wrap them up in a transaction so it will be all or nothing.
>> --daniel
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at postgis.refractions.net
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
ok it works....sometimes it is so easy...sorry for this silly question.
Do you know how I can sum the info of a column.
I want to save all avaible resources(all polygons) in an extra column 
next to the resources of one polygon?!

More information about the postgis-users mailing list