Pivotal Knowledge Base

Follow

SELECT UpdateGeometrySRID Fails with "Syntax Error"

Environment

 Product  Version
 Greenplum  4.3.x
 OS  RHEL 6.x

Symptom

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 "(" "

For 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

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)

Comments

Powered by Zendesk