From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | stan <stanb(at)panix(dot)com>, pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: Problems modifyiong view |
Date: | 2019-11-14 15:54:35 |
Message-ID: | 8ddebab4-a5fc-0c45-6096-fce8a56d0225@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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.
>
> regards, tom lane
>
--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2019-11-14 16:11:03 | Re: Problems modifyiong view |
Previous Message | Tom Lane | 2019-11-14 15:45:54 | Re: Problems modifyiong view |