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:30:33 |
Message-ID: | 9805c4d2-ce1f-30e7-1c23-f98a021b023f@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 11/14/19 7:12 AM, Tom Lane wrote:
> Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> 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(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2019-11-14 15:45:34 | Re: INOUT PARAMETERS WITH RETURN TABLES IN FUNCTION |
Previous Message | Kevin Brannen | 2019-11-14 15:14:57 | RE: ERROR: COPY escape must be a single one-byte character (multi-delimiter appears to work on Postgres 9.0 but does not on Postgres 9.2) |