[postgis-users] Renaming Raster Table

[Osahon Oduware]

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 <osahon.gis at gmail.com>
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 <osahon.gis at gmail.com>
> 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 <lr at pcorp.us> 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: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*.
>>>
>>> _______________________________________________
>>> postgis-users mailing list
>>> postgis-users at lists.osgeo.org
>>> https://lists.osgeo.org/mailman/listinfo/postgis-users
>>>
>>
>>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20170714/b4008f5d/attachment.html>