Search Postgresql Archives

Re: Problems modifyiong view

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On 11/14/19 7:54 AM, Adrian Klaver wrote:
On 11/14/19 7:45 AM, Tom Lane wrote:
Adrian Klaver <adrian.klaver@xxxxxxxxxxx> 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.klaver@xxxxxxxxxxx





[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux