Error when altering a table column using ST_Dump

Question

I user ALTER TABLE to change a Multilinestring geometry column to a Linestrings geometry column with this syntax:

ALTER TABLE jalan_diy
ALTER COLUMN geom TYPE Geometry(LINESTRING, 4326)
USING (ST_Dump(geom)).geom;

and I got an error like this...

ERROR: transform expression must not return a set
SQL state: 42804

Have you checked if this is a validation issue? Using st_makevalid(). — Encomium, Commented Jun 12, 2021 at 13:20
You cannot have set-returning functions in ALTER ... USING (and several other locations in SQL) for a multitude of reasons. If your Multi/Collection geometries your are attempting to re-type always have a single LineString as the only component, use ... USING ST_GeometryN(geom, 1). In every other case you will have to create a new table with the dumped results. — geozelot, Commented Jun 12, 2021 at 19:59
@Encomium i check it with ST_IsValid, everything seems ok... — astaga, Commented Jun 13, 2021 at 10:28
do u mean multilinestring with 1 geom fix by ST_GeometryN and multilinestrings with n geom fix by ST_Dump ?? can u give me some example? i try but still doesnt work ALTER TABLE jalan_diy ALTER COLUMN geom TYPE Geometry(LINESTRING, 4326) CASE WHEN ST_NumGeometries(geom) = 1 THEN USING ST_GeometryN(geom, 1) ELSE USING (ST_Dump(geom)).geom; AND ALTER TABLE jalan_diy ALTER COLUMN geom TYPE Geometry(LINESTRING, 4326) USING (ST_Dump(geom)).geom WHERE ST_NumGeometries(geom) > 1; — astaga, Commented Jun 13, 2021 at 13:25

astaga · Accepted Answer · 2021-06-13 16:45:27Z

0

i got the answer in this post a link

sorry, i dont know that i've to create a new table(temp/not)

answered Jun 13, 2021 at 16:45

astaga

1231 silver badge9 bronze badges

2

Please include relevant parts from the link in your answer, since links are subject to change and other users having the same problem wont be able to look into your solution.
– Erik
Commented Jun 16, 2021 at 14:34

Add a comment |

1 Answer 1