PostGIS view on multiple geometry columns

Question

I have two PostGIS tables, ‘filelinks’ and ‘maricopa_plss_qtrqtrs’. The filelinks table has a geometry type column named ‘geom_p’ and the maricopa_plss_qtrqtrs has a geometry type column named ‘geom’. The filelinks table can contain its own polygon geometry in geom_p, but if that geom_p value is null, then its ‘parent_ufid’ value will contain the ‘ufid’ value from maricopa_plss_qtrqtrs, which always has a populated geom field.

enter image description here

What I want to do is create a view (‘recursive_view’) that pulls the geometry from either the filelinks table geom_p (if it’s not null) or the maricopa_plss_qtrqtrs table’s geom value, related through the parent_ufid. Here’s what I have as the current view definition:

CREATE OR REPLACE VIEW gis.recursive_view AS
SELECT 
 f.ufid,
 qq.geom,
 f.geom_p,
 f.name,
 f.uri,
 f.type,
 f.date
FROM gis.filelinks f, gis.maricopa_plss_qtrqtrs qq
WHERE qq.ufid = f.parent_ufid or f.geom_p is not null;

The error I’m getting is:

ERROR: cannot change name of view column "geom_p" to "geom" ********** Error ********** ERROR: cannot change name of view column "geom_p" to "geom" SQL state: 42P16

I thought a PostGIS view can have multiple geometry columns, just like a table, no? What is the problem with my view definition then?

Answer 1

If you've already created the view, you cannot create/replace it and add a new column. You must either drop the view then re-create it or create a view with a different name.

• Rudy Stricklan: thank you. I don't see the view anywhere in the database, but maybe it is pending from the initial attempt. I'll try a new view name and then credit your solution.
• Rudy Stricklan: pdavis, that was indeed the problem. I wasn't seeing the view being displayed in pgAdmin because I wasn't refreshing the views. I thought the refresh tool in the main pgAdmin menu took care of everything, but apparently not. Thanks again.