On Fri, Mar 29, 2013 at 2:01 AM, Don Parris <parrisdc@gmail.com> wrote:
> Aha! As it turns out, I started looking into the character set support in
> the postgresql documentation, and discovered the psql -l command. It showed
> this test database is actually *not* encoded in UTF-8 at all, but rather in
> ASCII. I am not sure how I managed to do that, but I did. I was sure I had
> used the same DB creation script and just changed the DB name, but clearly,
> I missed something. I am not sure if it is necessary to drop and re-create
> the database to correct this, but that is what I have done.
>
> When I tried using \encoding or SET client_encoding, I got no errors, but I
> still saw this test DB set as ASCII when running the psql -l command.
> Anyway, I'll have to pursue this further later. Many thanks for the help!
In this case you should convert your database to utf8 (because it
contains utf8 data) asap. SQL_ASCII actually doesn't mean ASCII but
means store whatever octet you throw at it as it is, it's more akin to
binary data (but without the possibility to store 0x00). From your
examples, and with some luck, your database may contain utf8 only
data, but if you connect with different clients or encodings and feed
some latin1 etc. the database will be just happy to accept everything,
no question asked; just, it will be a nightmare to read the data back
or to make it uniform later.
If you don't have familiarity with encodings and relative problems,
the Spolsky article is a nice introduction
<http://www.joelonsoftware.com/articles/Unicode.html>.
-- Daniele