How To Fix Encoding Error In Postgres

Here is an example error when trying to create a database in Postgres:

ERROR 22023 (invalid_parameter_value) encoding "UTF8" does not match locale 
The chosen LC_CTYPE setting requires encoding "LATIN1".

Such an error might come up if OS locale was not set while installing Postgres. It is fixable by shuffling templates or by reinstalling Postgres after locale is set, I prefer the latest, cause having a fresh install just gives me more peace of mind. To fix that, we must first configure locale, on Debian/Ubuntu it would be something like that (the command will trigger an interactive prompt):

sudo dpkg-reconfigure locales

And then we need to remove Postgres completely, including the cluster generated after the first install. I used Ubuntu and was unable to achieve it by using apt remove or even apt purge, what worked for me is following:

sudo apt remove --purge postgresql*

That should do the trick, but just to make sure, let’s also delete few directories generated by Postgres:

sudo rm -r /etc/postgresql/
sudo rm -r /var/lib/postgresql/
sudo userdel -r postgres

That deletes a couple postgres directories and the user, there is also postgres group which will be deleted automatically cause it would be empty, but you can still try to delete it, just to be sure: “sudo groupdel postgres”.

After that, install postgres normally and it should re-create the cluster, with correct locale it will have unicode out of the box:

sudo apt install postgresql postgresql-contrib postgresql-doc

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.