Pivotal Knowledge Base

Follow

SELECT UpdateGeometrySRID Fails with "Syntax Error"

Environment

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