Multipoint to point tool in Qgis

Question

I have a multipoint adress layer creating problems with set-returning function cannot be used.

I tried :
Converting multipoint layer to point using PostGIS?
Multipoint vs point PostGIS

I tried converting the layer with vector > geometry tools > multipart to signle part in QGIS and sending it back to the PostGis dB but I still get the error. I tried as well by thicking the box "create single parts instead of multiparts" when sending the layer to postgis db with db manager in qgis but the error remains. And the type of my geometry is still MultiPoint:

I need this conversion to use ST_LineLocatePoint and get the following error. ERROR: line_locate_point: 2st arg isn't a point SQL state: XX000

Answer 1

If you want to do it with query, then you can possibly do it this way

1- create new column in your table with Point Type geometry

ALTER TABLE xyztable
  ADD COLUMN geom1 geometry (Point, SRID)

2- Now you need to update your the new column with converted geometries. You can get the point geometry using st_dump function, but you can not directly use it to update the geometry as update does not operate on the tuples So, it can be resolved by using this query

with temptable as
(select id, (ST_Dump(allp.points)).geom as gm
)
UPDATE xyztable 
set geom1= aa.gm
from temptable aa
where aa.id=xyztable.id;

3- and then you can rename your geom1 column to geom after deleting the original geom column.

Note: This all is to be done in postgis.

Answer 2

Select (ST_DumpPoints(geom)).geom from your_name_table