Re: Problems modifyiong view

Adrian Klaver <adrian.klaver@aklaver.com> writes:
> On 11/14/19 7:12 AM, Tom Lane wrote:
>> If you actually want to rename an existing view column, use
>> ALTER TABLE ... RENAME COLUMN ... for that.

> Alright, I'm missing something here:

> test=# alter table up_test rename COLUMN col1 to col_1;
> 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.col_1 AS col1,
>      up_test.col_2
>     FROM up_test;

Right, at this point the names of the underlying column and the view
column are out of sync, so the view definition must incorporate a
renaming AS to be correct.

> 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"

This is attempting to change the view output column's name to col_1
(since you didn't write "AS col1"), and it won't let you.  You could
do "ALTER TABLE test_view RENAME COLUMN col1 TO col_1" to put things
back in sync, if that's what you want.

            regards, tom lane