title: Converting in-place a Postgresql database from SQL_ASCII to UTF8
body: <p>(Not a duplicate of <a href="https://stackoverflow.com/questions/4079956/converting-a-postgresql-database-from-sql-ascii-containing-mixed-encoging-types">4079956</a>)</p>
  
  <p>I have an <code>SQL_ASCII</code> database, <code>LC_CTYPE</code>=<code>LC_COLLATION</code>=<code>"C"</code>, which contains mostly ASCII data as well as some non-ASCII characters from some codepage, say <code>LATIN1</code>.</p>
  
  <p>I want to transcode, <strong>in-place</strong> (no <code>pg_dump</code>/<code>pg-restore</code>), all non-ASCII codepoints from the <code>LATIN1</code> codepage to UTF-8 then alter the database encoding to UTF-8, e.g.:</p>
  
  <pre><code>-- change encoding first, transcode data after
  UPDATE pg_database SET encoding=pg_char_to_encoding('UTF8')
    WHERE datname='sqlasciidb';
  UPDATE tbl SET str=convert_from(str::bytea, 'LATIN1')
    WHERE str::bytea&lt;&gt;convert_from(str::bytea, 'LATIN1')::bytea;
  </code></pre>
  
  <p>or</p>
  
  <pre><code>-- transcode data first, change encoding after
  CREATE DOMAIN my_varlena AS bytea;
  CREATE CAST (my_varlena AS text) WITHOUT FUNCTION;
  UPDATE tbl SET str=convert(str::bytea, 'LATIN1','UTF8')::my_varlena::text
    WHERE str::bytea&lt;&gt;convert(str::bytea, 'LATIN1', 'UTF8');
  DROP DOMAIN my_varlena CASCADE;
  UPDATE pg_database SET encoding=pg_char_to_encoding('UTF8')
    WHERE datname='sqlasciidb';
  </code></pre>
  
  <p>What, if anything, is wrong with the above approach?</p>
  
  <p>Some problems I can see:</p>
  
  <ul>
  <li>after <code>pg_database</code> is updated, all connections to the database should be closed and reopened for the backend to take into account the new encoding</li>
  <li>all indexes based on the altered columns should be rebuilt</li>
  </ul>
  
  <p>Anything else?</p>