Initially I was storing my column as path and then following this question I changed it to text.
I'm trying to convert my column from text to geometry type by running this command:
ALTER TABLE area_grid_01 ALTER COLUMN coordinates TYPE Geometry(point, 4326) USING ST_SetSRID(coordinates::Geometry, 4326);
And facing this error:
ERROR: parse error - invalid geometry
HINT: "[(" <-- parse error at position 2 within geometry
SQL state: XX000
Following this question, I changed my data formatting and column looks like this:
The reason I am converting it into geometry in the first place is to be able to perform postgis functions on data.
I have explored many other ways trying to figure out a way around it. Seemingly, its not working yet!
Best Answer
I have no idea how you got the [] round what appears to be a textual representation of a LINESTRING, and there is a fairly strong clue in the error message. To remove them:
For example, using a simplified example of what you have above, [(1 1, 2 2, 3 3)], as I am too lazy to type out yours:
returns a linestring -- if you wrap this in ST_AsText, you get,
Note, the (a|b) syntax means match a and/or b the \ before the [ and ] is an escape character, as [ and ] have special a meaning in regular expressions, and g is a global flag, meaning replace everywhere.
Putting this altogether:
ought to do what you want, using your coordinates column instead of hard-coded numbers as above. The last bit is untested, so there may be an extra/missing ( or ) somewhere.