[postgis-users] DELETE old lines in a table

[P Kishor]

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
>



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
-----------------------------------------------------------------------
Assertions are politics; backing up assertions with evidence is science
=======================================================================
Sent from Madison, WI, United States