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?

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.

  • 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 Feb 12 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. – Rudy Stricklan Feb 12 at 4:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.