Re: Problems modifyiong view

On 11/14/19 7:54 AM, Adrian Klaver wrote:
> On 11/14/19 7:45 AM, Tom Lane wrote:
>> Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)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.

Or a link back to the ALTER TABLE section in the CREATE OR REPLACE VIEW
portion of:

https://www.postgresql.org/docs/11/sql-createview.html

>
>>
>>             regards, tom lane
>>
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com