How to join two line geometries together into a single record?

Question

I have a series of lines that I need to join together into a single record. Bellow is a mockup representation of my data. Some section numbers are duplicated and I need to update the geometries of the same section number into a single record.

Mockup Data:

id	section	geom
1	32	1234
2	32	1213
3	32	1231
4	33	3121

What I need:

id	section	geom
1	32	1234,1213,1231
4	33	3121

From what I have read ST_union is a good way to to this but can figure out how to update the rows. I think I need to subquery an update query but am unsure how to format this.

Sample Code:

UPDATE TABLE "tlbsections"
SELECT section,
       ST_Union(geom) as singlegeom
FROM "Ecorys_alkmaar_sections"
WHERE section = '32'
GROUP BY section
;

Answer 1

There are two options to do it

1. Insert union records into new table using below query -

insert into Ecorys_alkmaar_sections_1 (id,section,geom)
select min(id),section,ST_UNION(geom) as shape 
from Ecorys_alkmaar_sections
group by section

 Output of the above query will be - 

2. Update the existing table and delete duplicate records

You can use the below code to do the update based on the join condition

update Ecorys_alkmaar_sections t1
set geom = t2.geom 
from (select section,ST_UNION(geom) as shape 
from Ecorys_alkmaar_sections
group by section) t2 
where t1.section=t2.section

the output of the above command will be -

so you need to delete the duplicate records once the update is done.