I've imported a large table that has a geometry column, problem is that it is in text format, how to change this to be a point geometry in 27700 CRS?

I've tried SELECT UpdateGeometrySRID('table','geom',27700);

But got hit with

SQL Error [P0001]: ERROR: column not found in geometry_columns table Where: PL/pgSQL function updategeometrysrid(character varying,character varying,character varying,character varying,integer) line 35 at RAISE SQL statement "SELECT public.UpdateGeometrySRID('','',$1,$2,$3)" PL/pgSQL function updategeometrysrid(character varying,character varying,integer) line 5 at SQL statement

I've tried update table set geom2 = st_setsrid(ST_GeomFromText(geom),27700)

But got hit with

SQL Error [XX000]: ERROR: parse error - invalid geometry Hint: "" <-- parse error at position 1 within geometry

Is there a different way to change my column type to geometry?

  • What is the format of the values in that column?
    – CL.
    Commented Feb 16, 2021 at 11:11
  • @CL. the data type imported by ogr2ogr is text and the top returned cell contains POINT (535440 183402)
    – Luffydude
    Commented Feb 16, 2021 at 12:07

1 Answer 1


To change the type of a column, you need to use ALTER TABLE. Use NULLIF to convert empty strings into NULLs:

SET DATA TYPE geometry(geometry, 27700)   -- or geometry(point, 27700)
USING ST_SetSRID(ST_GeomFromText(NULLIF(geom, '')), 27700);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.