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
add comment

3 Answers

up vote 9 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
1  
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
add comment

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

share|improve this answer
add comment

Just re-arrange your columns selection, that will workout. I have fixed same error.

Before

create or replace view customer_orders as
    select
      o.id,
      c.name as customer_name, 
      c.name as customer_name,
      r.name as reseller_name, 
      CAST(o.order_status  AS varchar(20)) as order_status_name,
      o.customer_id,
      CAST(o.order_number AS VARCHAR(9)) as order_number,
      o.order_status ,
      o.created_by,
      (u.first_name|| ' ' ||u.last_name) as updated_by,
      o.created_at,
      o.updated_at
    from orders as o
    INNER JOIN customers as c ON c.id = o.customer_id"
    INNER JOIN customers as c ON c.id = o.customer_id INNER JOIN resellers as r ON r.id = c.reseller_id INNER JOIN users as u ON u.id = o.created_by

Error which I'm getting with above query -

PG::InvalidTableDefinition: ERROR: cannot change name of view column "created_by" to "reseller_name"

After, Below is the code which I re-arranged columns, its working well.

create or replace view customer_orders as
    select
      o.id,
      c.name as customer_name,
      CAST(o.order_status  AS varchar(20)) as order_status_name,
      o.customer_id,
      CAST(o.order_number AS VARCHAR(9)) as order_number,
      o.order_status,
      o.created_by,
      o.created_at,
      o.updated_at,
      r.name as reseller_name,
      (u.first_name|| ' ' ||u.last_name) as updated_by
    from orders as o
    INNER JOIN customers as c ON c.id = o.customer_id INNER JOIN resellers as r ON r.id = c.reseller_id INNER JOIN users as u ON u.id = o.created_by
share|improve this answer
    
How will this fix trying to rename columns in a view with create or replace? –  Colin 't Hart Feb 28 at 8:32
    
I have update my answer Colin, this is what I did. –  Rameshwar RV Feb 28 at 8:51
    
You're 1) changing order of columns, 2) dropping a column. When I try to do this in PG I get errors. See sqlfiddle.com/#!15/cd491 –  Colin 't Hart Feb 28 at 9:25
    
Quote from the documentation postgresql.org/docs/9.3/static/sql-createview.html : "CREATE OR REPLACE VIEW is similar, but if a view of the same name already exists, it is replaced. 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." So it's not possible -- are you using a different DBMS? –  Colin 't Hart Feb 28 at 9:27
add comment

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.