Thread: Problems modifyiong view
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"
I suppose I can drop the view, and recreate it, but that seems to indicate
that the create or replace functionality is not functioning the way I would
expect.
Am I missing something here?
--
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
-- Benjamin Franklin
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" > > I suppose I can drop the view, and recreate it, but that seems to indicate > that the create or replace functionality is not functioning the way I would > expect. > > 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." -- Adrian Klaver adrian.klaver@aklaver.com
Hi, On Thu, Nov 14, 2019 at 7:54 AM stan <stanb@panix.com> 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" > > I suppose I can drop the view, and recreate it, but that seems to indicate > that the create or replace functionality is not functioning the way I would > expect. > > Am I missing something here? What version? What OS server is running on? What client are you running? Thank you. > > -- > "They that would give up essential liberty for temporary safety deserve > neither liberty nor safety." > -- Benjamin Franklin > >
Adrian Klaver <adrian.klaver@aklaver.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.) regards, tom lane
On 11/14/19 7:12 AM, Tom Lane wrote: > Adrian Klaver <adrian.klaver@aklaver.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.klaver@aklaver.com
Adrian Klaver <adrian.klaver@aklaver.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.
regards, tom lane
On 11/14/19 7:45 AM, Tom Lane wrote: > Adrian Klaver <adrian.klaver@aklaver.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.klaver@aklaver.com
On 11/14/19 7:54 AM, Adrian Klaver wrote: > On 11/14/19 7:45 AM, Tom Lane wrote: >> Adrian Klaver <adrian.klaver@aklaver.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.klaver@aklaver.com
On Thu, Nov 14, 2019 at 06:31:48AM -0800, Adrian Klaver wrote: > 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" > > > > I suppose I can drop the view, and recreate it, but that seems to indicate > > that the create or replace functionality is not functioning the way I would > > expect. > > > > 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." OK, so I see this is documented behavior. This makes it a "feature", not a "bug" correct :-) Thanks. -- "They that would give up essential liberty for temporary safety deserve neither liberty nor safety." -- Benjamin Franklin
On Thu, Nov 14, 2019 at 10:12:22AM -0500, Tom Lane wrote: > Adrian Klaver <adrian.klaver@aklaver.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.) Thanks. I am just starting to explore this area at all. I thought I just added a column to a table, and did not realize that it was apended as the last column. Don't see why I care in that case, though. -- "They that would give up essential liberty for temporary safety deserve neither liberty nor safety." -- Benjamin Franklin