SELECT UpdateGeometrySRID Fails with "Syntax Error"

Products

• VMware Tanzu Greenplum

Issue/Introduction

Symptoms:

The customer installed PostGIS on the Greenplum server. They ran the following SQL query as well as postgres, but it failed with the syntax error below:

"ERROR: syntax error at or near "(" "

Example:

nakagawa=# SELECT UpdateGeometrySRID('public', 'testa', 'geom', 4326); 
ERROR: syntax error at or near "(" 
LINE 1: ...ABLE public.testa ALTER COLUMN geom TYPE geometry(Geometry,... 
^ 
QUERY: ALTER TABLE public.testa ALTER COLUMN geom TYPE geometry(Geometry, 4326) USING ST_SetSRID(geom,4326); 
CONTEXT: PL/pgSQL function "updategeometrysrid" line 80 at execute statement 
SQL statement "SELECT UpdateGeometrySRID('', $1 , $2 , $3 , $4 )" 
PL/pgSQL function "updategeometrysrid" line 4 at SQL statement

 

Environment

Cause

According to the function code as below, you can see the "enforce_srid_geom" constraints CHECK is necessary for this function:

80             :     ELSE 
 81             :         -- We will use typmod to enforce if no srid constraints
 82             :         -- We are using postgis_type_name to lookup the new name
 83             :         -- (in case Paul changes his mind and flips geometry_columns to return old upper case name)
 84             :         EXECUTE 'ALTER TABLE ' || quote_ident(real_schema) || '.' || quote_ident(table_name) ||
 85             :         ' ALTER COLUMN ' || quote_ident(column_name) || ' TYPE  geometry(' || postgis_type_name(myrec.type, myrec.coord_    dimension, true) || ', ' || new_srid::text || ') USING ST_SetSRID(' || quote_ident(column_name) || ',' || new_srid::text || ');' ;

 

Resolution

Recreate the table with "enforce_srid_geom" constraints CHECK.

-- Download the PostGIS from Network and install using the gppkg command:

gppkg -i /usr/local/postgis-ossv2.0.3_pv2.0.1_gpdb4.3orca-rhel5-x86_64.gppkg

-- Recreate the table with "enforce_srid_geom" constraints CHECK:

CREATE TABLE testa (
 id integer,
 geom geometry,
 CONSTRAINT enforce_srid_geom CHECK ((st_srid(geom) = 4326))
) DISTRIBUTED BY (id);

test=# \d+ testa
 Table "public.testa"
 Column | Type | Modifiers | Storage | Description
--------+----------+-----------+---------+-------------
 id | integer | | plain |
 geom | geometry | | main |
Check constraints:
 "enforce_srid_geom" CHECK (st_srid(geom) = 4326)
Has OIDs: no
Distributed by: (id)

-- Verify again:

test=# SELECT UpdateGeometrySRID('public', 'testa', 'geom', 4326);
 updategeometrysrid
----------------------------------------
 public.testa.geom SRID changed to 4326
(1 row)