A collation is an SQL schema object that maps an SQL name to operating system locales. A collation specifies the bit patterns that represent each character in a dataset and determine the rules used to sort and compare data. Collation attributes can have an impact on query results because they govern how sorting and comparison operations function.
To find the collation of the database, you need to query pg_database : select datname, datcollate from pg_database;
Recently I have updated my OS version from Ubunutu 20 to Ubuntu 22. After that whenever I tried to create a database in my Postgresql, it throws error. Below is the error shown:
The template database was created using collation version 2.31,
but the operating system provides version 2.35.
To solve that you have find all the databases in your localhost. List them and update their collation version one by one, by rebuilding all objects in the template database that use the default collation. Then you need to restart the POSTGRESQL service once. I am talking about the systemd service.
- Open postgres console
sudo -u postgres psql
- List down the databases:
\l - Execute for all the databases one by one. My DB name is sc_test
ALTER DATABASE sc_test REFRESH COLLATION VERSION;
- At last do it for postgres DB
ALTER DATABASE postgres REFRESH COLLATION VERSION;
- Restart the service:
sudo service postgresql restart - Now you can create your DB again.
create database fmdp_test