Re: Problems modifyiong view

On 11/14/19 7:45 AM, Tom Lane wrote:
> 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.

Aah. You do ALTER TABLE on the view, that was the part I missed.

Yeah an ALTER VIEW ... version of that would be more intuitive.

> 
>             regards, tom lane
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com