1

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?

1 Answer 1

3

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.

2
  • 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. Commented Feb 12, 2019 at 4:06
  • 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. Commented Feb 12, 2019 at 4:15

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.