Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

2 Answers 2

up vote 12 down vote accepted

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.

share|improve this answer
    
Yep, the error was only in "create or replace". Drop works. Thank you. –  Aleksandr Levchuk Jan 13 '11 at 4:42
    
Drop worked for me as well, anybody but got an explanation why create or replace does not work here? –  Matt Bannert Nov 6 '13 at 15:27
3  
The documentation postgresql.org/docs/9.3/static/sql-createview.html 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." –  Colin 't Hart Feb 28 at 9:28

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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