[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 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/72b552ce/attachment.html>