Thread: ERROR: cannot change name of view column
Changing the name of any column in a view gives the error "ERROR: cannot change name of view column".
The documentation is clear: "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)"
Reference 2: https://dba.stackexchange.com/questions/586/cant-rename-columns-in-postgresql-views-with-create-or-replace
However, ALTER TABLE ... RENAME COLUMN will change the view column name (see Reference 2 above).
Thus, the original error message is not completely correct. Ideally, the CREATE OR REPLACE VIEW would automatically apply the ALTER TABLE ... RENAME COLUMN (or underlying procedure) since it is available and fulfills the original DDL request, thus making the error message unnecessary.
Or at the very least, the error message could be changed to suggest using the ALTER TABLE ... RENAME COLUMN until the feature is included in the CREATE OR REPLACE VIEW.
PostgreSQL version 11.1
=?UTF-8?Q?R=C3=A9al_A=2E_Carbonneau?= <contact@realcarbonneau.com> writes:
> Changing the name of any column in a view gives the error "ERROR: cannot
> change name of view column".
In CREATE OR REPLACE VIEW, you mean? Yeah, that's intentional.
> Thus, the original error message is not completely correct. Ideally,
> the CREATE OR REPLACE VIEW would automatically apply the ALTER TABLE ...
> RENAME COLUMN (or underlying procedure) since it is available and fulfills
> the original DDL request, thus making the error message unnecessary.
DWIM doesn't seem like a good idea here to me. Doing what you suggest
would be far more likely to let mistakes get by undetected than to
actually do what the user intended. In many cases, the column types
are all alike, so that the column names are the only cross-check there
is that the new view definition matches up with the old.
For example, suppose you originally wrote
CREATE VIEW v AS SELECT x, y FROM ...
and then you want to change something in the FROM clause, but you
fat-finger the replacement command as
CREATE OR REPLACE VIEW v AS SELECT x, z FROM ...
If y and z are of the same datatype, this would pass undetected
if we try to be "helpful" in this way.
Another class of examples involves accidentally switching the order of
view output columns. This would interact especially badly with the
fact that we do allow CREATE OR REPLACE VIEW to append columns.
If you should have written
CREATE OR REPLACE VIEW v AS SELECT x, y, z FROM ...
to add "z" to the original list, but get the order wrong, this proposal
would allow very serious breakage of existing view users to occur.
(Previously-stored views will refer to those columns by number, not name,
which is really necessary to allow ALTER VIEW RENAME COLUMN to work at
all.)
> Or at the very least, the error message could be changed to suggest using
> the ALTER TABLE ... RENAME COLUMN until the feature is included in the
> CREATE OR REPLACE VIEW.
Perhaps there's room for a "HINT: if you intended to change view
column names, use ALTER VIEW RENAME COLUMN". But I suspect such
a hint would get printed in many cases where it didn't apply,
making it perhaps more confusing not less.
regards, tom lane
I appreciate the thoughtful feedback and appreciate your design perspective of providing more safety to the DBA using the DDL.
I haven't seen this kind of safety in other DBs. I haven't done an exhaustive review, however, I always had the impression it followed the English meaning of the command.
Since CREATE ... OR REPLACE is literally: "if exists, drop and create, else, create", I don't see any reason to have a safety. It not CREATE ... OR ALTER SAFELY, same for DROP, there is no safety, the dependent views will fail afterwards.
It's no problem for me, it was just a suggestion to bring the language closer to it's definition and more intuative from my humble perspective.
Close this issue as your team feels best.
Kind regards,
Rèal
On Tue, Jan 1, 2019, 13:18 Tom Lane <tgl@sss.pgh.pa.us wrote:
Réal A. Carbonneau <contact@realcarbonneau.com> writes:
> Changing the name of any column in a view gives the error "ERROR: cannot
> change name of view column".
In CREATE OR REPLACE VIEW, you mean? Yeah, that's intentional.
> Thus, the original error message is not completely correct. Ideally,
> the CREATE OR REPLACE VIEW would automatically apply the ALTER TABLE ...
> RENAME COLUMN (or underlying procedure) since it is available and fulfills
> the original DDL request, thus making the error message unnecessary.
DWIM doesn't seem like a good idea here to me. Doing what you suggest
would be far more likely to let mistakes get by undetected than to
actually do what the user intended. In many cases, the column types
are all alike, so that the column names are the only cross-check there
is that the new view definition matches up with the old.
For example, suppose you originally wrote
CREATE VIEW v AS SELECT x, y FROM ...
and then you want to change something in the FROM clause, but you
fat-finger the replacement command as
CREATE OR REPLACE VIEW v AS SELECT x, z FROM ...
If y and z are of the same datatype, this would pass undetected
if we try to be "helpful" in this way.
Another class of examples involves accidentally switching the order of
view output columns. This would interact especially badly with the
fact that we do allow CREATE OR REPLACE VIEW to append columns.
If you should have written
CREATE OR REPLACE VIEW v AS SELECT x, y, z FROM ...
to add "z" to the original list, but get the order wrong, this proposal
would allow very serious breakage of existing view users to occur.
(Previously-stored views will refer to those columns by number, not name,
which is really necessary to allow ALTER VIEW RENAME COLUMN to work at
all.)
> Or at the very least, the error message could be changed to suggest using
> the ALTER TABLE ... RENAME COLUMN until the feature is included in the
> CREATE OR REPLACE VIEW.
Perhaps there's room for a "HINT: if you intended to change view
column names, use ALTER VIEW RENAME COLUMN". But I suspect such
a hint would get printed in many cases where it didn't apply,
making it perhaps more confusing not less.
regards, tom lane
On Tuesday, January 1, 2019, Réal A. Carbonneau <contact@realcarbonneau.com> wrote:
Since CREATE ... OR REPLACE is literally: "if exists, drop and create, else, create",
It’s specifically saying “replace the query used for the view without changing the view signature/type”. Same goes for create or replace function. The point being the signature is public and cannot be changed on the fly but the implementation can be replaced.
David J.
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Tuesday, January 1, 2019, Réal A. Carbonneau <contact@realcarbonneau.com>
> wrote:
>> Since CREATE ... OR REPLACE is literally: "if exists, drop and create,
>> else, create",
> It’s specifically saying “replace the query used for the view without
> changing the view signature/type”.
Right, it's *not* a DROP. If it were, we'd make dependent views go away,
and the concern for preserving the view's rowtype would be much less.
> Same goes for create or replace
> function. The point being the signature is public and cannot be changed on
> the fly but the implementation can be replaced.
The analogy to functions is on-point here. We used to allow CREATE OR
REPLACE FUNCTION to change the names of function parameters, but we had to
disallow that (or at least greatly restrict it, I don't remember details
right now) when we started allowing calls that use parameter names to
disambiguate arguments.
regards, tom lane
Thank you all for your detailed responses and historical perspective on this.
If this issue is closed here, then pgAdmin has some work next to generate the appropriate ALTER statements when view column names change (of course this is a completely separate issue)! ;)
Kind regards,
Réal
On Tue, Jan 1, 2019, 15:23 Tom Lane <tgl@sss.pgh.pa.us wrote:
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Tuesday, January 1, 2019, Réal A. Carbonneau <contact@realcarbonneau.com>
> wrote:
>> Since CREATE ... OR REPLACE is literally: "if exists, drop and create,
>> else, create",
> It’s specifically saying “replace the query used for the view without
> changing the view signature/type”.
Right, it's *not* a DROP. If it were, we'd make dependent views go away,
and the concern for preserving the view's rowtype would be much less.
> Same goes for create or replace
> function. The point being the signature is public and cannot be changed on
> the fly but the implementation can be replaced.
The analogy to functions is on-point here. We used to allow CREATE OR
REPLACE FUNCTION to change the names of function parameters, but we had to
disallow that (or at least greatly restrict it, I don't remember details
right now) when we started allowing calls that use parameter names to
disambiguate arguments.
regards, tom lane