[postgis-users] field type "public.geometry"

[Phil Hurvitz]

> On Thu, Sep 20, 2012 at 06:51:02PM -0700, Phil Hurvitz wrote:
>> So what I think I want to do is install the updated types from the
>> postgis extension to the public schema, and then drop those types
>> from the gis schema. Looks like a real mess since I can't seem to
>> cast geometries in one schema to geometries in another schema.
>
> Have you tried something like this ?
>
>  ALTER TABLE tab ALTER COLUMN col TYPE public::geometry
>    USING public::geometry(col::text);
>
> --strk;
>
>  http://www.cartodb.com - Map, analyze and build applications with your data
>
>                                        ~~ http://strk.keybit.net

Sorry for the long delay, I had run into this again and decided it was 
time to solve it. As it turned out there were 2 different PostGIS 
installations in a single database. Some tables had geometry columns 
with the geometry type from the older version of in the public schema 
and some had newer geometry type in the 'gis' schema.

Sandro's suggestion didn't work, the column would not allow being 
ALTERed using that cast and others I tried.

I was able to achieve a fix by adding a new geometry column (making sure 
to use a SEARCH_PATH that had the 'gis' schema earlier in order, and then

UPDATE schema.table SET newcol = oldcol::text;

-- 
-P.

**************************************************************
Philip M. Hurvitz, PhD | Research Assistant Professor | UW-CBE
Urban Form Lab  | 1107 NE 45th Street, Suite 535  | Box 354802
University of Washington, Seattle, Washington  98195-4802, USA
phurvitz at u.washington.edu | http://gis.washington.edu/phurvitz
"What is essential is invisible to the eye." -de Saint-Exupéry
**************************************************************