Postgres change Encoding SQL_ASCII to UTF8

Question

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'

khampson · Accepted Answer · 2019-07-27 18:49:29Z

1

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.

answered Jul 27, 2019 at 18:49

khampson

15.5k4 gold badges45 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

aldo Over a year ago

so how to fix this invalid byte sequence for encoding "UTF8": 0xd9 0x27 during import the dump file?

khampson Over a year ago

@aldo: You would need to manually fix the encoding in the database for that occurrence such that the pg_dump and pg_restore will then succeed. It unfortunately may be a very iterative process. Exactly how you "fix" the encoding depends on your business use case(s).

Collectives™ on Stack Overflow

Postgres change Encoding SQL_ASCII to UTF8

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related