Creating polygon geometry from text field the same table in PostGiS

I have a table like this

 Table "public.zone_polygons"
  Column   |          Type           |        
-----------+-------------------------+
 id        | integer                 |
 zone_id   | integer                 | 
 zone_name | text                    | 
 zone_path | text                    | 
 geom      | geometry(Geometry,4326) |

Each zone_path has a list of lat longs as text in this format

75.2323 30.7423,
75.3432 30.5344,
75.5423 30.2342,
75.9123 30.3122,
75.2323 30.7423

I am trying to generate a geometry using the zone_path values using the below query.

update zone_polygons set geom=ST_SetSRID(ST_MakePolygon(ST_GeomFromText('LINESTRING(zone_path)')), 4326);

I get the below error

ERROR:  parse error - invalid geometry
HINT:  "LINESTRING(zo" <-- parse error at position 13 within geometry

Is there a way in postgis to use one of the fields to create geometry.

Solution

I believe you have a typo and the coordinates are in Long - Lat (India), not Lat-Long (middle of Barents sea). PostGIS expects coordinates as Long - Lat, so if the input list is indeed in lat-long, it would needs to be swapped. You can either fix the source or use ST_FlipCoordinates

Since the coordinates are saved in a column, you would need to concatenate the LINESTRING( and the column content (not name) using 'LINESTRING(' || zone_path || ')'

with src as (select '75.2323 30.7423, 75.3432 30.5344, 75.5423 30.2342, 75.9123 30.3122, 75.2323 30.7423' zone_path)
SELECT ST_ASTEXT(
   ST_SetSRID(
     ST_MakePolygon(
       ST_GeomFromText('LINESTRING(' || zone_path || ')')), 4326))
FROM src;


--> POLYGON((75.2323 30.7423,75.3432 30.5344,75.5423 30.2342,75.9123 30.3122,75.2323 30.7423))