[prev in list] [next in list] [prev in thread] [next in thread] 

List:       qgis-user
Subject:    Re: [Qgis-user] Create Postgres View for a St_Union PostGIS
From:       Erik Wienhold via QGIS-User <qgis-user () lists ! osgeo ! org>
Date:       2023-01-20 13:38:41
Message-ID: 1568628090.211973.1674221922000 () office ! mailbox ! org
[Download RAW message or body]

> On 20/01/2023 13:46 CET 1520 gis via QGIS-User <qgis-user@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
_______________________________________________
QGIS-User mailing list
QGIS-User@lists.osgeo.org
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user

[prev in list] [next in list] [prev in thread] [next in thread]