Subtract MultiPolygon table from LineString table

Question

I've two tables, one with a roadnetwork and another one with some polygon. The roadnetwork table has a geometry column defined as

COLUMN geom geometry(LineString,4326);

while the polygon table has a geometry column defined as

COLUMN geom geometry(MultiPolygon,4326);

I want to perform a Boolean operation, by subtracting the polygon geometry from the roadnetwork geometry, in order to make an hole inside my roadnetwork.

I've tried the following query, that I've found in stackoverflow:

UPDATE public.roadnetwork
SET geom = ST_Multi(ST_Difference(public.roadnetwork.geom, public.sub_polygon.geom))
FROM public.sub_polygon
WHERE ST_Intersects(public.roadnetwork.geom, public.sub_polygon.geom);

But I got the following error:

subtractpolygon.sql:4: ERROR:  Geometry type (MultiLineString) does not match column type (LineString)

How can I subtract the polygons defined in polygon table from the roadnetwork table?

EDIT:

If I run this query:

SELECT ST_AsText(ST_Difference(public.roadnetwork.geom, public.sub_polygon.geom))
FROM public.roadnetwork, public.sub_polygon
WHERE ST_Intersects(public.roadnetwork.geom, public.sub_polygon.geom)

I obtain some results, in which there are both LINESTRING and GEOMETRYCOLLECTION EMPTY:

 GEOMETRYCOLLECTION EMPTY
 GEOMETRYCOLLECTION EMPTY
 LINESTRING(51.5118811558203 25.3205662647387,51.512059 25.3202912,51.512134 25.3201413,51.5121684 25.320028,51.5121684 25.319968,51.5121396 25.3198288)
 GEOMETRYCOLLECTION EMPTY
 LINESTRING(51.5128903 25.3196673,51.5129743 25.3198392,51.5130642 25.3199292,51.5132841 25.3200641,51.5136939 25.320329,51.5139688 25.3205439,51.5143176 25.3208249,51.5145508 25.321047,51.5146999055034 25.3211917269465)
 GEOMETRYCOLLECTION EMPTY
 GEOMETRYCOLLECTION EMPTY
 GEOMETRYCOLLECTION EMPTY
 LINESTRING(51.5170740202354 25.331309846686,51.5170939 25.3329918,51.5171189 25.3333467,51.5171813 25.3336378,51.5172913 25.3339226,51.5174162 25.3341775,51.5175362 25.3343724,51.5176661 25.3345324,51.5178627 25.3346364)

I think that when I update the geom column I got the error because I've empty GEOMETRYCOLLECTION in query results, so it cannot be inserted in geom table that wants LineString. How can I change the query in order to filter unwanted data?

EDIT2

I've updated my query in order to remove empty geometry collections that don't affect my results:

WITH RESULTS as (
SELECT ST_Difference(public.roadnetwork.geom, public.sub_polygon.geom)
FROM public.roadnetwork, public.sub_polygon
),

 FILTERED_RESULTS as (
  SELECT * FROM RESULTS WHERE RESULTS.st_difference <> '0107000020E610000000000000'
)

 UPDATE public.roadnetwork
 SET geom = FILTERED_RESULTS.st_difference FROM FILTERED_RESULTS;

The problem is that now I've the following error

Geometry type (MultiLineString) does not match column type (LineString)

In this case I can't filter MultiLineString because I need these data. Probably they are results of ST_Difference because I've not them in my roadnetrowk table. So I need to find a way to process FILTERED_RESULTS in my query in order to replace MultiLinestring with corresponding LineString. I've tried ST_LineMerge but with no change in my data.

Problem in graphic way

I've this road network and this polygon:

I want to obtain this:

Answer 1

That's because that only makes sense. If you have a line 0-10, and you remove the range 4-6 you have two lines. You represent that in one geometry as a multiline.

You say,

The roadnetwork table has a geometry column defined as COLUMN geom geometry(LineString,4326);

And then you go on to show in your example..

UPDATE public.roadnetwork
SET geom = ST_Multi()

You can't update public.roadnetwork to a MULTILINESTRING when it's strongly typed on the table to be LineString.

If none of that makes sense look at this..

SELECT ST_AsText(ST_SymDifference(l1,l2)), ST_AsText(l1)
FROM ( VALUES
  (
    ST_MakeLine( ST_MakePoint(0,0),ST_MakePoint(0,10) ),
    ST_MakeLine( ST_MakePoint(0,4),ST_MakePoint(0,6) )
  )
) AS t(l1,l2);

Here we bisect 0,10 removing 4,6. This produces MULTILINESTRING((0 0,0 4),(0 6,0 10)). If you try to insert that into the table typed for a LineString,that holds a LINE(0 10) you would expect an error. And, that's what you're getting.

If none of that makes sense just observe that this doesn't work,

CREATE TABLE foo ( geom geometry(LineString) );

INSERT INTO foo (geom) VALUES ( 
  ST_Multi(
    ST_MakeLine(ST_MakePoint(0,0),ST_MakePoint(0,10))
  )
);
ERROR:  Geometry type (MultiLineString) does not match column type (LineString)

To break a MULTILINESTRING up use ST_Dump

SELECT ST_AsText(d.geom)
FROM ST_GeomFromText('MULTILINESTRING((0 0, 0 1),(0 2,0 3))') AS t
CROSS JOIN LATERAL ST_Dump(t) AS d;
      st_astext      
---------------------
 LINESTRING(0 0,0 1)
 LINESTRING(0 2,0 3)
(2 rows)

Or just set the type of the column to MULTILINESTRING, and elevate all of the LINESTRINGS to MULTILINESTRINGS using ST_Multi