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

by Aleksandr Levchuk   Last Updated August 14, 2019 07:06 AM

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?

Tags : postgresql


Answers 2


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.

Joe
Joe
January 13, 2011 02:19 AM

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

Peter Eisentraut
Peter Eisentraut
January 13, 2011 07:12 AM

Related Questions


Updated April 09, 2018 20:06 PM

Updated June 06, 2017 18:06 PM

Updated May 15, 2017 19:06 PM

Updated March 15, 2017 02:06 AM