[postgis-users] Renaming Raster Table

Regina Obe lr at pcorp.us
Fri Jul 7 13:20:04 PDT 2017


Osahon,

 

I think the only place where the name of table is explicitly referenced is in the constraint called enforce_overview_rast that is on each of the overview tables.

The raster_overviews view uses the information in this constraint to populate the parent table.

 

I forget why we decided not to go by name instead of table oid.  If we did, then renaming the tables would be sufficient.

 

Anyway to rename the tables, do the following:

 

1)      Rename the tables as you would normally with

ALTER TABLE ned RENAME TO ned_2017;

ALTER TABLE o_3_ned RENAME TO o_3_ned_2017;

 

Etc.

2)      Drop the constraint on each over view table and read it.

So for example if your table is in schema public, and you renamed it to o_3_ned_2017

 

You'd do:

 

ALTER TABLE public.o_3_ned_2017 DROP CONSTRAINT enforce_overview_rast;

 

ALTER TABLE public.o_3_nj_ned_2017

  ADD CONSTRAINT enforce_overview_rast CHECK (_overview_constraint(rast, 3, 'public'::name, 'ned_2017'::name, 'rast'::name));

 

 

If you have a lot of these, it's fairly easy to script them by querying the raster_overview table and executing the outputs of this query.  NOT tested so you might need to fiddle with it.

 

SELECT  'ALTER TABLE ' || quote_ident(o_table_schema)  || '.' || quote_ident(o_table_name) || ' DROP CONSTRAINT enforce_overview_rast; 

ALTER TABLE ' ||  quote_ident(o_table_schema)  || '.' || quote_ident(o_table_name) || ' ADD CONSTRAINT enforce_overview_rast 

CHECK (_overview_constraint(rast, ' || overview_factor || ', ' || quote_literal(r_table_schema) || '::name, ' || quote_literal('mynewtablename') || ', ' || quote_literal(o_raster_column) || '::name)); '  AS sql FROM  raster_overviews;

 

 

Hope that helps,

Regina

http://postgis.us

 

 

From: postgis-users [mailto:postgis-users-bounces at lists.osgeo.org] On Behalf Of Osahon Oduware
Sent: Friday, July 07, 2017 3:55 AM
To: PostGIS Users Discussion <postgis-users at lists.osgeo.org>
Subject: [postgis-users] Renaming Raster Table

 

Hi All,

 

I used the raster2pgsql tool to load a raster with overviews into a PostGIS database using the following format:

raster2pgsql -s <SRID> -d -Y -e -I -C -F -M -l 2,4,8,16 /path/to/raster/file <table_name> | psql -h <host_address> -U postgres -p 5432 -d <database>

 

Now, I want to rename the raster table and I would like to know the affected tables and the required changes to be done without affecting the functionality of the raster/overviews.

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


More information about the postgis-users mailing list