Problems modifyiong view

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
10 messages Options
Reply | Threaded
Open this post in threaded view
|

Problems modifyiong view

stan-9
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


Reply | Threaded
Open this post in threaded view
|

Re: Problems modifyiong view

Igor Korot
Hi,

On Thu, Nov 14, 2019 at 7:54 AM stan <[hidden email]> 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
>
>


Reply | Threaded
Open this post in threaded view
|

Re: Problems modifyiong view

Adrian Klaver-4
In reply to this post by stan-9
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
[hidden email]


Reply | Threaded
Open this post in threaded view
|

Re: Problems modifyiong view

Tom Lane-2
Adrian Klaver <[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|

Re: Problems modifyiong view

Adrian Klaver-4
On 11/14/19 7:12 AM, Tom Lane wrote:

> Adrian Klaver <[hidden email]> 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
[hidden email]


Reply | Threaded
Open this post in threaded view
|

Re: Problems modifyiong view

Tom Lane-2
Adrian Klaver <[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|

Re: Problems modifyiong view

Adrian Klaver-4
On 11/14/19 7:45 AM, Tom Lane wrote:

> Adrian Klaver <[hidden email]> 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
[hidden email]


Reply | Threaded
Open this post in threaded view
|

Re: Problems modifyiong view

Adrian Klaver-4
On 11/14/19 7:54 AM, Adrian Klaver wrote:

> On 11/14/19 7:45 AM, Tom Lane wrote:
>> Adrian Klaver <[hidden email]> 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
[hidden email]


Reply | Threaded
Open this post in threaded view
|

Re: Problems modifyiong view

stan-9
In reply to this post by Adrian Klaver-4
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


Reply | Threaded
Open this post in threaded view
|

Re: Problems modifyiong view

stan-9
In reply to this post by Tom Lane-2
On Thu, Nov 14, 2019 at 10:12:22AM -0500, Tom Lane wrote:

> Adrian Klaver <[hidden email]> 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