convert text field to utf8 in sql_ascii database

From: Andy Colson <andy(at)squeakycode(dot)net>
To: PostgreSQL <pgsql-general(at)postgresql(dot)org>
Subject: convert text field to utf8 in sql_ascii database
Date: 2011-11-17 18:48:34
Message-ID: 4EC55702.4030701@squeakycode.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi All.

I am in the middle of a process to get all my data into utf8. As its
not all converted yet, my database encoding is SQL_ASCII.

I am getting external apps fixed up to write utf8 to the database, and
so far so good. But, I ran across some stuff that needs a one time
convert, and wanted to just write sql to update it.

I cant seem to figure it out. I'm on Slackware 64, PG 9.0.4.

I tried:
update general set legal = convert(legal, 'windows_1250', 'utf8');

ERROR: function convert(text, unknown, unknown) does not exist
HINT: No function matches the given name and argument types. You might
need to add explicit type casts.

It looks like convert() only does bytea, and legal is defined as text.
Can't seem to cast it either (as legal::bytea). The result is bytea, so
again would have to cast/convert?

Also, I'd like to add, I dont understand the online help:
http://www.postgresql.org/docs/9.0/static/functions-string.html

shows convert(string, src, dest), but then says see Table 9-7, which has
conversion names, like windows_1250_to_utf8, where do I use that?

The help also shows functions convert_to() and convert_from(). But I
dont understand how to use them.

update general set legal = convert_to(legal, 'utf8');

How does it know what source encoding to use? And it converts to bytea
so how do I convert it back to text?

I'm a little confused. I know I'm probably not doing this "the right
way" (c), I'm trying to get there eventually, but for now, I cant just
drop my database and recreate it as utf8.

Any hints?

-Andy

Responses

Browse pgsql-general by date

  From Date Subject
Next Message John R Pierce 2011-11-17 18:53:52 Re: connection manager pgpool... help
Previous Message Tomas Vondra 2011-11-17 18:37:43 Re: Please recommend me the best bulk-delete option