37

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?

44

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.

  • Indeed, you have to add the new colum to the end of the SELECT column list, otherwise you'll get the error! – user4584 Mar 24 '15 at 8:55
  • 1
    A little bit off-topic comment: I've faced the similar issue when tried to aggregate data from two tables with aid of view. But indeed I had different types (VARCHAR in the first table, and TEXT in the second table) for the same named columns. I spent a lot of time for investigation until I noticed that IDE displays VARCHAR for both tables! Only pure pg_dump helped me. – flaz14 Jun 23 '16 at 11:01
30

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

  • What is tbl here? is it actual table for which view is created or this is view name? – Eugen Konkov Jan 17 at 11:48

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.