Postgresql – Can’t rename columns in PostgreSQL views with CREATE OR REPLACE

postgresql

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?

Best Answer

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.

Related Solutions

Postgresql – Postgres 9.2 – Create View Different Column Names

addr:housename is an illegal column name to begin with. You could have only created that if you used a quoted identifier (see the manual for details)

To create a view and rename the column, simply provide a column alias in the view's query:

create or replace view v_some_view
as
select ... other columns ...
       "addr:housename" as addr_housename
from some_table;

Postgresql replace table without losing dependancies

Disable any FKs pointing to the table
Create table SAVE as SELECT * FROM A
Truncate table A
Begin to create a script with the following

with xfer as (select cast ('a' as text) as targ_tab, cast ('b' as text) as from_tab)
select xfer.*, tabs.table_name as targ_table_name, cols.table_name as from_table_name,
  'alter table ' || tabs.table_name || ' add (' ||
  coalesce(cols.column_name, '') || ' ' || coalesce (cols.data_type, '')
  ||  
      case when data_type = 'numeric' and coalesce (cols.numeric_precision,
          cols.numeric_scale) is not null then
          '(' || coalesce ('' || cols.numeric_precision, '*') || ', ' ||
          coalesce (cols.numeric_scale, 0) || ')'
      else ''
      end
  || ')' 
  as synt
from xfer join
  information_schema.tables tabs on (xfer.targ_tab = tabs.table_name) 
  join
  information_schema.columns cols on (xfer.from_tab = cols.table_name)
where not exists (
select 1
from information_schema.columns targcols
where targcols.table_name = xfer.targ_tab
and targcols.column_name = cols.column_name
)
order by ordinal_position

Repopulate with INSERT/SELECT
Re-enable foreign keys

Best Answer

Related Solutions

Postgresql – Postgres 9.2 – Create View Different Column Names

Postgresql replace table without losing dependancies

Related Question