Using `CAST` and `ST_UNION` in same query in PostgreSQL

Question

I want to union the geometry of waterlines grouped by a table column in a new view. My problem is that I apparently needes to specify the geometry type using CAST function. But I cannot figure how to use the CAST function in combination with ST_UNION.

CREATE OR REPLACE VIEW _06_spildevand_vandloeb.drikkevandsledning_dissolve as
 SELECT row_number() over () as id, 
 ST_UNION(the_geom)::geometry(MULTILINESTRING,25832), 
 descr 
 FROM _06_spildevand_vandloeb.ledninger_vand
 GROUP BY descr

This results in, though, that the name of geometry column is called ST_UNION, so I must have got the syntax wrong when using CAST and ST_UNION.

What is the problem?

• Maximilien jaffrès: I think casting is not really relevant. just : "St_Union(geom) as geom" should do the trick, the result will be cast in the simplest geometry possible, so multilinestring or linestring. However, you can still enforce it and use ::geometry(MULTILINESTRING, 25832)

Answer 1

You need to alias your columns, effectively giving it a name, if they are the result of a function, or PostgreSQL will use the function name of the outermost function it uses to generate the column (values)!

CREATE OR REPLACE VIEW _06_spildevand_vandloeb.drikkevandsledning_dissolve AS
 SELECT ROW_NUMBER() OVER() AS id, 
 ST_Union(the_geom)::GEOMETRY(MULTILINESTRING,25832) AS geom, -- alias AS geom
 descr 
 FROM _06_spildevand_vandloeb.ledninger_vand
 GROUP BY
 descr
;