postgis - Take from multilinestring the start and end points

I have to use the A* algorithm and my geometry is in multilinestring and I want to take stratpoint(x1,y1) and endpoint(x2,y2) . I use this queries but the columns after the execute is still empty

UPDATE roads SET x1 = ST_x(ST_startpoint(geom));
UPDATE roads SET y1 = ST_y(ST_startpoint(geom));

UPDATE roads SET x2 = ST_x(ST_endpoint(geom));
UPDATE roads SET y2 = ST_y(ST_endpoint(geom));

after the execution give this message

Query returned successfully: 2584 rows affected, 101 ms execution time.

Then I make a select and the columns are empty

I searched the net and I didn't find anything, I don't know what I can do.

Answer

If your roads really consist of multi-geometries, then you will run into troubles with pgRouting. Then it's necessary to change your multi-linestrings into multiple regular linestrings. As already mentioned, you can use ST_Dump to achieve this.

But in reality I have rarely seen real multi-linestrings even if the geometry type says so. This is because shp2pgsql for example imports line data by default as "multilinestring" geometry type if you didn't specify explicitly to use LineString, even if there is no multi-geometry.

So I would first test, if you really have multi-linestrings with:

SELECT COUNT(
        CASE WHEN ST_NumGeometries(geom) > 1 THEN 1 END
    ) AS multi, COUNT(geom) AS total 
FROM roads;

In case multi is 0 you have no multi-linestrings at all and you can just update your geometry type to Linestring with your 1st geometry using ST_GeometryN:

ALTER TABLE roads

    ALTER COLUMN geom TYPE geometry(LineString, 4326) 
    USING ST_GeometryN(geom, 1);