0

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?

Arnold Cristobal
  • 843
  • 2
  • 16
  • 36
  • Unrelated, but: Postgres 9.1 is [no longer supported](https://www.postgresql.org/support/versioning/) you should plan an upgrade to the current version as soon as possible. –  Jul 31 '19 at 09:12

1 Answers1

2

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

  • The string concatenation operator is || in SQL, not +.
  • Square brackets are invalid for identifiers in SQL.
  • Postgres has no CLUSTERED indexes.

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: