Re: Problems modifyiong view

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.)

regards, tom lane