postgispostgresql-9.1sqlgeometry

Convert Geometry from T-SQL to Postgres

I have this column for creating a geometry from SQL Server:

Shape  AS ([geometry]::STGeomFromText(((('POINT('+CONVERT([varchar](20);,Longitude))+' ')+CONVERT([varchar](20),Latitude))+')',(4326))), PRIMARY KEY CLUSTERED

I need to create the column from SQL Server to Postgres:

CREATE EXTENSION postgis;

CREATE EXTENSION postgis_topology;

I also changed the STGeomFromText to ST_GeomFromText but I'm getting an error in pgAdmin:

ERROR:  syntax error at or near "AS"
LINE 32:  Shape  AS ([geometry]::STGeomFromText(((('POINT('+CONVERT([...
             ^

********** Error **********

ERROR: syntax error at or near "AS"
SQL state: 42601
Character: 818

Did I missed something to install for postgis or the syntax needs a complete overhaul for postgres?

Solution

  • That syntax is completely invalid for Postgres (and not valid standard SQL either). It seems you blindly copied that definition from SQL Server

    But most importantly: Postgres currently (Postgres 11) has no computed (generated) columns so you can't define a column as a computed on using Shape AS <expression>

    They will be available in the upcoming version 12 (to be released in Q4/2019).

    Related: