CODEWITHSUNDEEP
postgresqlpostgresql-9.5
Enes
Enes

Reputation: 341

Postgres change Encoding SQL_ASCII to UTF8

i want to change database encoding sql ascii to utf8 without drop database it is possible? (psql 9.5) (I'm afraid of data disruption)

I'm trying this but doesn't work 

 update pg_database set encoding = pg_char_to_encoding('utf8') where datname = 'name'

postgre

Upvotes: 2

Views: 3455

Answers (1)

khampson
khampson

Reputation: 15306

You don't have to necessarily drop the database. However, you will need to effectively create another one with the new encoding, so changing the encoding really isn't possible to do without data disruption.

In general, the preferred method is to use pg_dump to dump the current database, create a new database with utf8 encoding, and do a pg_restore to that database.

However, along the way, you may find that there are data-related problems that must be fixed in order for the pg_restore to not fail, as there can be many things stored in a sql_ascii-encoded database (which despite its name is really the absence of any encoding -- it basically just takes the bytes as they are) that are not valid UTF8.

Upvotes: 1

Related Questions