Hi Regina, I needed a way to effectively rename a raster table (and associated overviews), because, I was finding it difficult to use the raster2pgsql tool to create a raster table with upper-case characters as described/resolved in the link below: Raster Table Name With Upper-Case Characters <https://groups.google.com/forum/#%21searchin/postgis-users/Raster$20Table$20Name$20With$20Upper-Case$20Characters%7Csort:relevance/postgis-users/rTblFqEajG0/3xcvJ6nfBQAJ>
Maybe I would have to reload the raster from scratch since I now know how to create raster tables with upper-case characters using the raster2pgsql tool (see link above). The raster is quite large and took a very long time to create, hence, I would have preferred a faster and efficient way of renaming the already existing table/overviews. On Fri, Jul 14, 2017 at 1:40 PM, Osahon Oduware <[email protected]> wrote: > Hi Regina, > > I needed a way to effectively rename a raster table (and associated > overviews), because, I was finding it difficult to use the raster2pgsql > tool to create a raster table with upper-case characters as > described/resolved in the link below: > Raster Table Name With Upper-Case Characters > <https://groups.google.com/forum/#%21searchin/postgis-users/Raster$20Table$20Name$20With$20Upper-Case$20Characters%7Csort:relevance/postgis-users/rTblFqEajG0/3xcvJ6nfBQAJ> > > Maybe I would have to reload the raster from scratch since I now know how > to create raster tables with upper-case characters using the raster2pgsql > tool (see link above). The raster is quite large and took a very long time > to create, hence, I would have prepared a faster and efficient way of > renaming the already existing table/overviews. > > On Wed, Jul 12, 2017 at 6:03 PM, Osahon Oduware <[email protected]> > wrote: > >> Hi Regina, >> >> I have followed the steps outlined in your post and I could alter the >> raster and overview table successfully. However, *when I tried to load >> the renamed raster in QGIS it gives this error*: >> "Cannot get GDAL raster band:" >> >> Please, how do I go about fixing this error? >> >> On Fri, Jul 7, 2017 at 9:20 PM, Regina Obe <[email protected]> wrote: >> >>> 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:[email protected]] *On >>> Behalf Of *Osahon Oduware >>> *Sent:* Friday, July 07, 2017 3:55 AM >>> *To:* PostGIS Users Discussion <[email protected]> >>> *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*. >>> >>> _______________________________________________ >>> postgis-users mailing list >>> [email protected] >>> https://lists.osgeo.org/mailman/listinfo/postgis-users >>> >> >> >
_______________________________________________ postgis-users mailing list [email protected] https://lists.osgeo.org/mailman/listinfo/postgis-users
