[GIS] Convert string column back to geometry column

Question

I had a table with a column set to 'point' in doctrine2, but this was changed to 'string' and updated.

I can't change it back to 'point' in doctrine2, I get an error:

[Doctrine\DBAL\Exception\DriverException]
  An exception occurred while executing 'ALTER TABLE opr_records ALTER the_geom TYPE public.geometry':
  SQLSTATE[42804]: Datatype mismatch: 7 ERROR:  column "the_geom" cannot be cast automatically to type geometry
  HINT:  Specify a USING expression to perform the conversion.

[Doctrine\DBAL\Driver\PDOException]
  SQLSTATE[42804]: Datatype mismatch: 7 ERROR:  column "the_geom" cannot be cast automatically to type geometry
  HINT:  Specify a USING expression to perform the conversion.

[PDOException]
  SQLSTATE[42804]: Datatype mismatch: 7 ERROR:  column "the_geom" cannot be cast automatically to type geometry
  HINT:  Specify a USING expression to perform the conversion.

There is already data in the column for example:

id,geom
0,0101000020E610000091AE217D7A881A40551A3D49BD424740
1,0101000020E61000009F6A8C15600F1B407EDF9D1DCA3E4740
2,0101000020E6100000525C9D4D06831A40F459A7CCFF424740

I could connect to the DB and perform the update manually, however I am unsure on the SQL to perform ? Something like:

ALTER TABLE opr_records ALTER COLUMN the_geom TYPE geometry USING ST_geom(the_geom);

Answer 1

The function st_geom does not exist.

Try this :

ALTER TABLE opr_records ALTER COLUMN the_geom TYPE geometry(Point) USING st_astext(the_geom);