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
