<div dir="ltr"><div><div>Hi Regina,<br><br></div>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:<br><a href="https://groups.google.com/forum/#%21searchin/postgis-users/Raster$20Table$20Name$20With$20Upper-Case$20Characters%7Csort:relevance/postgis-users/rTblFqEajG0/3xcvJ6nfBQAJ" target="_blank">Raster Table Name With Upper-Case Characters</a><br><br></div>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 <span style="background-color:rgb(255,255,0)">preferred</span> a faster and efficient way
of renaming the already existing table/overviews.</div><div class="gmail_extra"><br><div class="gmail_quote">On Fri, Jul 14, 2017 at 1:40 PM, Osahon Oduware <span dir="ltr"><<a href="mailto:osahon.gis@gmail.com" target="_blank">osahon.gis@gmail.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir="ltr"><div><div><div>Hi Regina,<br><br></div>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:<br><a href="https://groups.google.com/forum/#%21searchin/postgis-users/Raster$20Table$20Name$20With$20Upper-Case$20Characters%7Csort:relevance/postgis-users/rTblFqEajG0/3xcvJ6nfBQAJ" target="_blank">Raster Table Name With Upper-Case Characters</a><br><br></div>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.<br></div><div><div class="h5"><div><br><div><div><div><div><div><div><div><div><div class="gmail_extra"><div class="gmail_quote">On Wed, Jul 12, 2017 at 6:03 PM, Osahon Oduware <span dir="ltr"><<a href="mailto:osahon.gis@gmail.com" target="_blank">osahon.gis@gmail.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir="ltr"><div><div><div>Hi Regina,<br><br></div>I have followed the steps outlined in your post and I could alter the raster and overview table successfully. However, <b>when I tried to load the renamed raster in QGIS it gives this error</b>:<br></div>"Cannot get GDAL raster band:"<br><br></div><div>Please, how do I go about fixing this error?<br></div></div><div class="gmail_extra"><br><div class="gmail_quote"><span class="m_5015402316770643351gmail-">On Fri, Jul 7, 2017 at 9:20 PM, Regina Obe <span dir="ltr"><<a href="mailto:lr@pcorp.us" target="_blank">lr@pcorp.us</a>></span> wrote:<br></span><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div><div class="m_5015402316770643351gmail-h5"><div lang="EN-US"><div class="m_5015402316770643351gmail-m_-576600057290092655m_-8604119229146358703WordSection1"><p class="MsoNormal"><span style="font-size:11pt;font-family:"Calibri",sans-serif;color:rgb(31,73,125)">Osahon,<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:"Calibri",sans-serif;color:rgb(31,73,125)"><u></u> <u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:"Calibri",sans-serif;color:rgb(31,73,125)">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.<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:"Calibri",sans-serif;color:rgb(31,73,125)">The raster_overviews view uses the information in this constraint to populate the parent table.<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:"Calibri",sans-serif;color:rgb(31,73,125)"><u></u> <u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:"Calibri",sans-serif;color:rgb(31,73,125)">I forget why we decided not to go by name instead of table oid. If we did, then renaming the tables would be sufficient.<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:"Calibri",sans-serif;color:rgb(31,73,125)"><u></u> <u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:"Calibri",sans-serif;color:rgb(31,73,125)">Anyway to rename the tables, do the following:<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:"Calibri",sans-serif;color:rgb(31,73,125)"><u></u> <u></u></span></p><p class="m_5015402316770643351gmail-m_-576600057290092655m_-8604119229146358703MsoListParagraph"><u></u><span style="font-size:11pt;font-family:"Calibri",sans-serif;color:rgb(31,73,125)"><span>1)<span style="font:7pt "Times New Roman""> </span></span></span><u></u><span style="font-size:11pt;font-family:"Calibri",sans-serif;color:rgb(31,73,125)">Rename the tables as you would normally with<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:"Calibri",sans-serif;color:rgb(31,73,125)">ALTER TABLE ned RENAME TO ned_2017;<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:"Calibri",sans-serif;color:rgb(31,73,125)">ALTER TABLE o_3_ned RENAME TO o_3_ned_2017;<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:"Calibri",sans-serif;color:rgb(31,73,125)"><u></u> <u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:"Calibri",sans-serif;color:rgb(31,73,125)">Etc.<u></u><u></u></span></p><p class="m_5015402316770643351gmail-m_-576600057290092655m_-8604119229146358703MsoListParagraph"><u></u><span style="font-size:11pt;font-family:"Calibri",sans-serif;color:rgb(31,73,125)"><span>2)<span style="font:7pt "Times New Roman""> </span></span></span><u></u><span style="font-size:11pt;font-family:"Calibri",sans-serif;color:rgb(31,73,125)">Drop the constraint on each over view table and read it.<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:"Calibri",sans-serif;color:rgb(31,73,125)">So for example if your table is in schema public, and you renamed it to o_3_ned_2017<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:"Calibri",sans-serif;color:rgb(31,73,125)"><u></u> <u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:"Calibri",sans-serif;color:rgb(31,73,125)">You'd do:<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:"Calibri",sans-serif;color:rgb(31,73,125)"><u></u> <u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:"Calibri",sans-serif;color:rgb(31,73,125)">ALTER TABLE public.o_3_ned_2017 DROP CONSTRAINT enforce_overview_rast;<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:"Calibri",sans-serif;color:rgb(31,73,125)"><u></u> <u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:"Calibri",sans-serif;color:rgb(31,73,125)">ALTER TABLE public.o_3_nj_ned_2017<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:"Calibri",sans-serif;color:rgb(31,73,125)"> ADD CONSTRAINT enforce_overview_rast CHECK (_overview_constraint(rast, 3, 'public'::name, 'ned_2017'::name, 'rast'::name));<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:"Calibri",sans-serif;color:rgb(31,73,125)"><u></u> <u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:"Calibri",sans-serif;color:rgb(31,73,125)"><u></u> <u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:"Calibri",sans-serif;color:rgb(31,73,125)">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.<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:"Calibri",sans-serif;color:rgb(31,73,125)"><u></u> <u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:"Calibri",sans-serif;color:rgb(31,73,125)">SELECT 'ALTER TABLE ' || quote_ident(o_table_schema) || '.' || quote_ident(o_table_name) || ' DROP CONSTRAINT enforce_overview_rast; <u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:"Calibri",sans-serif;color:rgb(31,73,125)">ALTER TABLE ' || quote_ident(o_table_schema) || '.' || quote_ident(o_table_name) || ' ADD CONSTRAINT enforce_overview_rast <u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:"Calibri",sans-serif;color:rgb(31,73,125)">CHECK (_overview_constraint(rast, ' || overview_factor || ', ' || quote_literal(r_table_schema) || '::name, ' || quote_literal('mynewtablename'<wbr>) || ', ' || quote_literal(o_raster_column) || '::name)); ' AS sql FROM raster_overviews;<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:"Calibri",sans-serif;color:rgb(31,73,125)"><u></u> <u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:"Calibri",sans-serif;color:rgb(31,73,125)"><u></u> <u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:"Calibri",sans-serif;color:rgb(31,73,125)">Hope that helps,<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:"Calibri",sans-serif;color:rgb(31,73,125)">Regina<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:"Calibri",sans-serif;color:rgb(31,73,125)"><a href="http://postgis.us" target="_blank">http://postgis.us</a><u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:"Calibri",sans-serif;color:rgb(31,73,125)"><u></u> <u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:"Calibri",sans-serif;color:rgb(31,73,125)"><u></u> <u></u></span></p><p class="MsoNormal" style="margin-left:0.5in"><b><span style="font-size:11pt;font-family:"Calibri",sans-serif">From:</span></b><span style="font-size:11pt;font-family:"Calibri",sans-serif"> postgis-users [mailto:<a href="mailto:postgis-users-bounces@lists.osgeo.org" target="_blank">postgis-users-bounces@<wbr>lists.osgeo.org</a>] <b>On Behalf Of </b>Osahon Oduware<br><b>Sent:</b> Friday, July 07, 2017 3:55 AM<br><b>To:</b> PostGIS Users Discussion <<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><wbr>><br><b>Subject:</b> [postgis-users] Renaming Raster Table<u></u><u></u></span></p><div><div class="m_5015402316770643351gmail-m_-576600057290092655h5"><p class="MsoNormal" style="margin-left:0.5in"><u></u> <u></u></p><div><div><p class="MsoNormal" style="margin-left:0.5in"><span style="font-size:9.5pt">Hi All,<u></u><u></u></span></p></div><div><p class="MsoNormal" style="margin-left:0.5in"><span style="font-size:9.5pt"><u></u> <u></u></span></p></div><div><p class="MsoNormal" style="margin-left:0.5in"><span style="font-size:9.5pt">I used the raster2pgsql tool to load a raster with overviews into a PostGIS database using the following format:<u></u><u></u></span></p></div><div><p class="MsoNormal" style="margin-left:0.5in"><span style="font-size:9.5pt">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><u></u><u></u></span></p></div><div><p class="MsoNormal" style="margin-left:0.5in"><span style="font-size:9.5pt"><u></u> <u></u></span></p></div><div><p class="MsoNormal" style="margin-left:0.5in"><span style="font-size:9.5pt">Now, <b>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</b>.<u></u><u></u></span></p></div></div></div></div></div></div><br></div></div><span class="m_5015402316770643351gmail-">______________________________<wbr>_________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<a href="https://lists.osgeo.org/mailman/listinfo/postgis-users" rel="noreferrer" target="_blank">https://lists.osgeo.org/mailma<wbr>n/listinfo/postgis-users</a><br></span></blockquote></div><br></div>
</blockquote></div><br></div></div></div></div></div></div></div></div></div></div></div></div></div>
</blockquote></div><br></div>