Can't rename columns in PostgreSQL views with CREATE OR REPLACE

Question

In PostreSQL 8.3, I'm trying to create a view which will look just like an existing table but have different column names.

This works

CREATE OR REPLACE VIEW gfam.nice_builds AS 
 SELECT (family_tree.family_tree_id) as x,
        family_tree.family_tree_name, family_tree.family_tree_description
   FROM gfam.family_tree;

The above makes a duplicate of the family_tree table but the following attempt fails:

CREATE OR REPLACE VIEW gfam.nice_builds AS 
 SELECT (family_tree.family_tree_id) as x,
        family_tree.family_tree_name, family_tree.family_tree_description
   FROM gfam.family_tree;

• ERROR: cannot change name of view column "family_tree_id"

How can rename the columns?

Answer 1

I can reproduce your error ... in my case, I created a column first as 'date' then as 'x' (was trying to see if it was an issue with a reserved word; it wasn't:

ERROR:  cannot change name of view column "date" to "x"

If you issue a drop view first, it'll let you re-create the view with a changed name. I have no idea why create or replace won't do it.

Answer 2

You can use ALTER TABLE tbl RENAME COLUMN foo TO bar to rename view columns as well.