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 I rename 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.

---

Clarification by Colin 't Hart:

The documentation for CREATE VIEW explains it pretty well, I think:

The new query must generate the same columns that were generated by the existing view query (that is, the same column names in the same order and with the same data types), but it may add additional columns to the end of the list.

• user4584: Indeed, you have to add the new colum to the end of the SELECT column list, otherwise you'll get the error!

Answer 2

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

Answer 3

This is because the view is already saved with the column names. Delete the saved view and then run the modified view with the new 'AS' column names.