How do you change the character encoding of a postgres database?

by

First off, Daniel’s answer is the correct, safe option.

For the specific case of changing from SQL_ASCII to something else, you can cheat and simply poke the pg_database catalogue to reassign the database encoding. This assumes you’ve already stored any non-ASCII characters in the expected encoding (or that you simply haven’t used any non-ASCII characters).

Then you can do:

update pg_database set encoding = pg_char_to_encoding('UTF8') where datname="thedb"

This will not change the collation of the database, just how the encoded bytes are converted into characters (so now length('£123') will return 4 instead of 5). If the database uses ‘C’ collation, there should be no change to ordering for ASCII strings. You’ll likely need to rebuild any indices containing non-ASCII characters though.

Caveat emptor. Dumping and reloading provides a way to check your database content is actually in the encoding you expect, and this doesn’t. And if it turns out you did have some wrongly-encoded data in the database, rescuing is going to be difficult. So if you possibly can, dump and reinitialise.

More Related Contents:

  1. Convert escaped Unicode character back to actual character in PostgreSQL
  2. How does the search_path influence identifier resolution and the “current schema”
  3. How do you find the row count for all your tables in Postgres
  4. How do you use variables in a simple PostgreSQL script?
  5. How to make “case-insensitive” query in Postgresql?
  6. Rownum in postgresql
  7. How to switch databases in psql?
  8. Postgresql: Scripting psql execution with password
  9. dynamic sql query in postgres
  10. Cannot create a database table named ‘user’ in PostgreSQL
  11. Is there a timeout for idle PostgreSQL connections?
  12. How to make connection to Postgres via Node.js
  13. docker postgres pgadmin local connection
  14. Does a Postgres UNIQUE constraint imply an index?
  15. SELECT raises exception in PL/pgSQL function
  16. Find difference between timestamps in seconds in PostgreSQL
  17. Unable to connect PostgreSQL to remote database using pgAdmin
  18. How does PostgreSQL perform ORDER BY with a b-tree index on the field?
  19. How can I change a PostgreSQL user password?
  20. Docker wait for postgresql to be running
  21. I forgot the password I entered during PostgreSQL installation
  22. SQL query column does not exist error
  23. Postgresql enforce unique two-way combination of columns
  24. When do Postgres column or table names need quotes and when don’t they?
  25. How can I get a list of all functions stored in the database of a particular schema in PostgreSQL?
  26. Composite PRIMARY KEY enforces NOT NULL constraints on involved columns
  27. how to store PostgreSQL jsonb using SpringBoot + JPA?
  28. Orbeon Forms Postgres DB connection
  29. Postgres default sort by id – worldship
  30. Best way to install hstore on multiple schemas in a Postgres database?

Leave a Comment