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

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