Matthias Apitz wrote: > Thanks. I did \l before which gives: > > List of databases > Name | Owner | Encoding | Collate | Ctype | ICU Locale > | Locale Provider | Access privileges > ------------+----------+----------+-------------+-------------+------------+-----------------+----------------------- > bar | foo | UTF8 | de_DE.UTF-8 | de_DE.UTF-8 | > | libc | > customers | sisis | UTF8 | de_DE.UTF-8 | de_DE.UTF-8 | > | libc | > ... > > But why the ALTER statement needs the spelling different as the output of > \l : > > sisis=# ALTER COLLATION "de_DE.UTF-8" REFRESH VERSION; > ERROR: collation "de_DE.UTF-8" for encoding "UTF8" does not exist The "Collate" and "Ctype" columns in the output of \l refer to a locale name that is passed to libc to use locale-dependant functions. It's somewhat counter-intuitive, but it's technically not a database collation, and it's independent from the de_DE.utf8 collation that exists in the database, in the sense that de_DE.utf8 is the name of a database object whereas a locale is not a database object. In fact, if your applications always use the default collation like most apps do (that is, it never uses explicit COLLATE clauses), then you could even issue DROP COLLATION "de_DE.utf8" or ALTER COLLATION "de_DE.utf8" RENAME TO "foobar" and it would not have any notable effect. \l would still report "de_DE.UTF-8" as it did previously. That's because "de_DE.utf8" is not the default collation, it's a collation that happens to correspond to the same locale as the default collation. The default collation is named "default", it lives in the "pg_catalog" namespace, and it cannot be dropped since it's a system object. Technically the ALTER DATABASE xxx REFRESH COLLATION VERSION updates the pg_database.datcollversion field (for PG15+, before that it did not exist), whereas the ALTER COLLATION xxx REFRESH VERSION updates the pg_collation.collversion field. With PG15+, ALTER DATABASE xxx REFRESH COLLATION VERSION does not imply any ALTER COLLATION. If you do only the ALTER DATABASE, all the collations in pg_collation still have their collversion fields that lag behind. But it only matters if these collations are actually used by explicit COLLATE clauses, otherwise Postgres will never use them and thus never emit any warning. Best regards, -- Daniel Vérité https://postgresql.verite.pro/
