Re: Converting an ASCII database to an UTF-8 database

I have this exact problem. I have dumped and reloaded other
databases and set the client encoding to convert them to UTF-8 but I
have one database with values that still cause it to fail, even if I
specify that the client encoding is SQL_ASCII. How do I fix that?

On Feb 17, 2006, at 4:08 PM, Ragnar wrote:

> On fös, 2006-02-17 at 22:38 +0100, Peter Eisentraut wrote:
>> kishore(dot)sainath(at)gmail(dot)com wrote:
>>> How do I convert a database in the ASCII format into one of the
>>> UTF-8
>>> format?
>>
>> ASCII is a subset of UTF-8, so you don't need to do anything. Just
>> change the encoding entry in the pg_database table. Of course, using
>> pg_dump would be the official way to convert a database between
>> any two
>> encodings.
>
> This will only work correctly if the database
> definitely does not contain non-ASCII characters.
>
> Assuming by ASCII format we mean that the database was
> created SQL_ASCII, then it is possible that it contains
> invalid UTF-8 characters, as SQL_ASCII is a 8 bit
> encoding.
>
> consider:
>
> template1=# create database test with encoding='SQL_ASCII';
> CREATE DATABASE
> template1=# \connect test
> You are now connected to database "test".
> test=# create table a (x text);
> CREATE TABLE
> test=# insert into a values ('á');
> INSERT 33304378 1
> test=# select * from a;
> x
> ---
> á
> (1 row)
>
> test=# update pg_database set encoding =
> pg_catalog.pg_char_to_encoding('UTF8') where datname='test';
> UPDATE 1
> test=# select * from a;
> x
> ---
> á
> (1 row)
>
> test=# \connect template1
> You are now connected to database "template1".
> template1=# \connect test
> You are now connected to database "test".
> test=# select * from a;
> x
> ---
>
> (1 row)
>
> test=#
>
>
> gnari
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>