Re: Problems modifyiong view - Mailing list pgsql-general

On 11/14/19 7:12 AM, Tom Lane wrote:
> Adrian Klaver <adrian.klaver@aklaver.com> writes:
>> On 11/14/19 5:53 AM, stan wrote:
>>> I am trying to add columns to a view using CREATE OR REPLACE VIEW, and I am
>>> getting the following error:
>>> ERROR:  cannot change name of view column "descrip" to "contact_person_1"
>>> Am I missing something here?
> 
>> https://www.postgresql.org/docs/11/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. The calculations
>> giving rise to the output columns may be completely different."
> 
> Yeah, the important point being that you can only add columns at the
> *end* of the view, just like you can only add table columns at the
> end.  The same-names-and-types check is intended to catch simple
> mistakes in this area.
> 
> If you actually want to rename an existing view column, use
> ALTER TABLE ... RENAME COLUMN ... for that.  (We probably ought
> to offer an ALTER VIEW spelling of that, but we don't ATM.
> ALTER TABLE works though.)

Alright, I'm missing something here:

test=# \d up_test
               Table "public.up_test"
  Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
  id     | integer |           |          |
  col1   | boolean |           |          |
  col_2  | integer |


ALTER TABLE
test=# \d+ test_view
                           View "public.test_view"
  Column |  Type   | Collation | Nullable | Default | Storage | Description
--------+---------+-----------+----------+---------+---------+-------------
  id     | integer |           |          |         | plain   |
  col1   | boolean |           |          |         | plain   |
  col_2  | integer |           |          |         | plain   |
View definition:
  SELECT up_test.id,
     up_test.col1,
     up_test.col_2
    FROM up_test;



test=# alter table up_test rename COLUMN col1 to col_1;
ALTER TABLE
test=# \d up_test
               Table "public.up_test"
  Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
  id     | integer |           |          |
  col_1  | boolean |           |          |
  col_2  | integer |

test=# \d+ test_view
                           View "public.test_view"
  Column |  Type   | Collation | Nullable | Default | Storage | Description
--------+---------+-----------+----------+---------+---------+-------------
  id     | integer |           |          |         | plain   |
  col1   | boolean |           |          |         | plain   |
  col_2  | integer |           |          |         | plain   |
View definition:
  SELECT up_test.id,
     up_test.col_1 AS col1,
     up_test.col_2
    FROM up_test;


test=# create or replace view test_view as select id, col_1 , col_2 from 
up_test;
ERROR:  cannot change name of view column "col1" to "col_1"

The underlying table column name changes, but the view column is aliased 
to the original column name.

> 
>             regards, tom lane
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com