[postgis-users] OIDs and AddGeometryColumn

[strk at refractions.net]

On Tue, Dec 06, 2005 at 04:43:24PM -0800, Dylan Keon wrote:
> Hi all,
> 
> I upgraded a server to PostgreSQL 8.1.0 and PostGIS 1.0.5 (coming from
> 7.4.5/0.8.2 - ancient, I know :).  I can't do a SELECT
> AddGeometryColumn, apparently because there are no OID columns in the
> restored tables.  The default behavior in 8.1 is to not create OIDs.
> 
>   tsunami=# SELECT AddGeometryColumn('temp','geom',-1,'POINT',2);
>   ERROR:  column reference "oid" is ambiguous
> 
> Do I have to force OID creation just so that I can use
> AddGeometryColumn?  Or am I missing something?

You need OID just in the geometry_columns table.
Neither pg_restore nor postgis_restore.pl will do this
for you, unfortunately. 

I suggest you backup your geometry_columns table, recreate
it using the CREATE TABLE found in lwpostgis.sql and finally
populate the new one from the backup.

Maybe this could be encoded into the postgis_restore.pl script.

--strk;


> 
> Thanks much,
> Dylan
> 
> 
> Full output:
> 
> tsunami=# SELECT AddGeometryColumn('temp','geom',-1,'POINT',2);
> ERROR:  column reference "oid" is ambiguous
> CONTEXT:  SQL statement "UPDATE geometry_columns SET f_table_schema =
> n.nspname FROM pg_namespace n, pg_class c, pg_attribute a,
> pg_constraint sridcheck, pg_constraint typecheck WHERE (
> f_table_schema is NULL OR f_table_schema = '' OR f_table_schema NOT IN
> ( SELECT nspname::varchar FROM pg_namespace nn, pg_class cc,
> pg_attribute aa WHERE cc.relnamespace = nn.oid AND cc.relname =
> f_table_name::name AND aa.attrelid = cc.oid AND aa.attname =
> f_geometry_column::name)) AND f_table_name::name = c.relname AND c.oid
> = a.attrelid AND c.relnamespace = n.oid AND f_geometry_column::name =
> a.attname AND sridcheck.conrelid = c.oid AND sridcheck.consrc LIKE
> '(srid(% = %)' AND sridcheck.consrc ~ textcat(' = ', srid::text) AND
> typecheck.conrelid = c.oid AND typecheck.consrc LIKE
> '((geometrytype(%) = ''%''::text) OR (% IS NULL))' AND
> typecheck.consrc ~ textcat(' = ''', type::text) AND NOT EXISTS (
> SELECT oid FROM geometry_columns gc WHERE c.relname::varchar =
> gc.f_table_name AND n.nspname::varchar = gc.f_table_schema AND
> a.attname::varchar = gc.f_geometry_column )"
> PL/pgSQL function "fix_geometry_columns" line 17 at SQL statement
> SQL statement "SELECT  fix_geometry_columns()"
> PL/pgSQL function "addgeometrycolumn" line 142 at select into variables
> SQL statement "SELECT  AddGeometryColumn('','', $1 , $2 , $3 , $4 , $5 )"
> PL/pgSQL function "addgeometrycolumn" line 4 at select into variables
> tsunami=#
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users

-- 

 /"\    ASCII Ribbon Campaign
 \ /    Respect for low technology.
  X     Keep e-mail messages readable by any computer system.
 / \    Keep it ASCII.