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

[Stephen Woodbridge]

On 2/8/2013 1:34 AM, Stefan Schwarzer wrote:
> 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!

Stefan,

Load the table to something like table_new then in a transaction

begin;
alter table rename table_old to table_bak;
alter table rename table_new to table_old;
end;

Another way to do this is:

create schema new;

-- shp2pgsql .... new.my_table

create schema tmp;

begin;
alter table current_schema.my_table set schema tmp;
alter table new.my_table set schema current_schema;
end;

Now your original table and related indexes are in schema tmp
and your new table is in current_schema. If you do this on a live 
database using the transactions are atomic and anybody using that table 
will not be impacted by the change other than they they will magically 
be working with the new data.

-Steve