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?

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
;