On 11/14/19 7:12 AM, Tom Lane wrote:
Adrian Klaver <adrian.klaver@xxxxxxxxxxx> writes:
On 11/14/19 5:53 AM, stan wrote:
I am trying to add columns to a view using CREATE OR REPLACE VIEW, and I am
getting the following error:
ERROR: cannot change name of view column "descrip" to "contact_person_1"
Am I missing something here?
https://www.postgresql.org/docs/11/sql-createview.html
"CREATE OR REPLACE VIEW is similar, but if a view of the same name
already exists, it is replaced. The new query must generate the same
columns that were generated by the existing view query (that is, the
same column names in the same order and with the same data types), but
it may add additional columns to the end of the list. The calculations
giving rise to the output columns may be completely different."
Yeah, the important point being that you can only add columns at the
*end* of the view, just like you can only add table columns at the
end. The same-names-and-types check is intended to catch simple
mistakes in this area.
If you actually want to rename an existing view column, use
ALTER TABLE ... RENAME COLUMN ... for that. (We probably ought
to offer an ALTER VIEW spelling of that, but we don't ATM.
ALTER TABLE works though.)
Alright, I'm missing something here:
test=# \d up_test
Table "public.up_test"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
id | integer | | |
col1 | boolean | | |
col_2 | integer |
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.col1,
up_test.col_2
FROM up_test;
test=# alter table up_test rename COLUMN col1 to col_1;
ALTER TABLE
test=# \d up_test
Table "public.up_test"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
id | integer | | |
col_1 | boolean | | |
col_2 | integer |
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;
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"
The underlying table column name changes, but the view column is aliased
to the original column name.
regards, tom lane
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
