[postgis-users] Problem with UpdateGeometrySRID

[Marco Boeringa]

Hi,

I am running into a weird issue I haven't encountered before with 
UpdateGeometrySRID.

I have a table that correctly shows up in the 'geometry_columns' view 
with the following data:

* f_table_schema=osm

* f_table_name=tmpDissolve0

* f_geometry_column=way

* coord_dimension=2

* srid=4326

* type=GEOMETRY

However, if I look in DBeaver in the table column view, it only shows:

  'way(geometry)'

as column type, not:

'way(geometry(geometry(4326))'

as expected and seen on other tables in DBeaver's column view.

I have tried to fix this by running:

SELECT UpdateGeometrySRID('osm','tmpDissolve0','way',4326);

from within a larger Python script, but this unexpectedly errors out with:

"column not found in geometry_columns table
CONTEXT:  PL/pgSQL function updategeometrysrid(character 
varying,character varying,character varying,character varying,integer) 
line 35 at RAISE
SQL statement "SELECT public.UpdateGeometrySRID('',$1,$2,$3,$4)"
PL/pgSQL function updategeometrysrid(character varying,character 
varying,character varying,integer) line 5 at SQL statement"

When I tried the "alternative", as mentioned on the Help page of 
UpdateGeometrySRID (https://postgis.net/docs/UpdateGeometrySRID.html), 
which according to the Help text should be equivalent:

ALTER TABLE osm.tmpDissolve0 ALTER COLUMN way TYPE 
geometry(GEOMETRY,4326) USING ST_SetSRID(way,4326);

this succeeds, and I see correctly:

'way(geometry(geometry(4326))'

in DBeaver.

My main question: why does UpdateGeometrySRID fail here, while the 
equivalent code succeeds?

Version information:

Postgres version: 15.3 (Ubuntu 15.3-1.pgdg22.04+1)
PostGIS version: POSTGIS="3.3.2 4975da8" [EXTENSION] PGSQL="150" 
GEOS="3.10.2-CAPI-1.16.0" PROJ="8.2.1" LIBXML="2.9.13" LIBJSON="0.15" 
LIBPROTOBUF="1.3.3" WAGYU="0.5.0 (Internal)"

Marco