The following bug has been logged on the website:
Bug reference: 17404
Logged by: Pierre-Aurélien GEORGES
Email address: pageorge@unice.fr
PostgreSQL version: 12.4
Operating system: Debian 8.3.0-6 (x86_64-pc-linux-gnu)
Description:
3 STEPS TO REPRODUCE :
========================
create TABLE my_table (my_text TEXT);
create VIEW my_view AS SELECT my_text COLLATE "C" FROM my_table;
create OR REPLACE view my_view AS SELECT my_text COLLATE "en_US.utf8" FROM
my_table;
EXPECTED RESULT :
===================
The COLLATION of "my_view"."my_text" should have been changed to
"en_US.utf8".
- OR -
I should have got an error message telling me that it is not possible.
OBSERVED RESULT :
===================
Nothing (it silently fails).
select table_schema, table_name, column_name, collation_name from
information_schema.columns where collation_name is not null and
table_name='my_view';
clearly shows that the COLLATION has not been changed as requested.
What the doc says about CREATE OR REPLACE VIEW :
=============================================
"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*."
It doesn't say wether the COLLATION is considered being part of the "data
types" or not, i.e. it doesn't say wether it's possible to change the
COLLATION of an existing field or not.
My tests (under Postgres v. 12.4) trying to do so showed that postgres says
nothing and seems to accept the SQL statement, but nothing is changed in the
database (it silently fails). From the user perspective, I consider such a
silent failure the WORST situation possible : I would prefer either 1) to
get an error message saying that it's not possible to change the COLLATION
of an existing field - OR even better - 2) to actually change the COLLATION
of the existing field, as requested.
For this reason, I have also reported a documentation issue.