[postgis-users] Renaming Raster Table

[Osahon Oduware]

Hi Regina,

Sorry for my late response, I have been away on another task. I would try
out what you suggested and give you feedback. Thanks a lot.

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/20170712/c282dbdb/attachment.html>