[postgis-users] How to update (replace) a Postgis table?

[Paolo Crosato]

Il 08/02/2013 07:34, Stefan Schwarzer ha scritto:
>
> Hi there,
>
> I would like to "update" a "countries of the world"-postgis-table, by 
> replacing it with another one which comes in form of a shapefile 
> (dumped from a Postgis database) from my development server.
>
> Now, I can't just do a "pgsql2shp", as this would throw an error as 
> the table already exists. And renaming the existing table into 
> something like "xx_original" doesn't work either ("xx_geom_gist 
> already exists", is then the error message).
>
> I don't want to delete the (old) table directly, as I would like to 
> keep it as a backup.
>
> So, what is the "right" way to solve this? Thanks for any hints!
>
> Stef
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
Hi,

I think the best way to solve the issue would be to version the table 
and refer to it by a synonim.
Let's say your table name is world_countries, rename it to 
world_countries_2012 and create a synonim to it, like
CREATE SYNONIM TABLE world_countries ON world_countries_2012

Then load the new one with a name like world_countries_2013, and to 
switch to it simply change the synonim.

This way you can keep the same name on the queries and update tables 
keeping the previous ones.

Regards,

Paolo

-- 
Paolo Crosato

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20130208/c68082d9/attachment.html>