[Qgis-user] Create Postgres View for a St_Union PostGIS

[Erik Wienhold]

> On 20/01/2023 13:46 CET 1520 gis via QGIS-User <qgis-user at lists.osgeo.org> wrote:
>
> Dear users,
>
> I have run a SQL Select [1] in order to Dissolve the features in a PostGIS
> table using ST_Union. I can load the Select [1] result as a new layer in QGIS,
> but when I try to create a PostgreSQL View [2] for the Select [1], I get an
> error message: [ERROR: cannot change name of view column "geom" to "id". Would
> anyone give me a hint or direction to solve this problem?

This means that view geointeligencia.vw_cpma_limites already exists with geom as
its first column.

CREATE OR REPLACE VIEW can only add new columns at the end but not rename
existing columns or change their type.  You need to drop the view first.

> [1]
> 
> --CREATE OR REPLACE VIEW geointeligencia.vw_cpma_limites AS
> (
> SELECT 
> row_number() over() AS id, 
> st_union(mue_cpma.geom)::geometry(MultiPolygon,4674) as geom
> FROM( 
> SELECT *
> FROM geo.br_municipios_2021 
> WHERE 
> NM_MUN IN (
> 'Afonso Cunha',
> 'Água Doce do Maranhão',
> 'Alcântara',
> 'Aldeias Altas',
> 'Altamira do Maranhão'
> )
> AND 
> SIGLA IN('MA')) AS mue_cpma
> );
> 
> [2]
> CREATE OR REPLACE VIEW geointeligencia.vw_cpma_limites AS
> (
> SELECT 
> row_number() over() AS id, 
> st_union(mue_cpma.geom)::geometry(MultiPolygon,4674) as geom
> FROM( 
> SELECT *
> FROM geo.br_municipios_2021 
> WHERE 
> NM_MUN IN (
> 'Afonso Cunha',
> 'Água Doce do Maranhão',
> 'Alcântara',
> 'Aldeias Altas',
> 'Altamira do Maranhão'
> )
> AND 
> SIGLA IN('MA')) AS mue_cpma 

--
Erik