Re: Database Encoding - Mailing list pgsql-novice

M. Bastin wrote:

> Hi Brad,
>
> As far as I know the database encoding is set when you create the
> database and can't be altered afterwards.  (I'm pretty sure of this
> since it's written explicitly in the PostgreSQL 7.3.2 docs.)
>
> It shouldn't be too hard to do the conversion by recreating your db
> however (unless you had a really complicated one).  Perhaps someone
> can suggest a tool to save your database structure and then re-apply
> it on your new db.
>
> With pgSQL4RB you can just use the COPY TO/FROM STDIN feature
> (explained in the pgSQL4RB manual under backup/restore and in the
> pgsql 7.3 docs) which should allow you to transfer all your data in a
> few seconds time.  Just make sure you include OIDs if you're using
> them for your relations, and don't rebuild your indexes till after you
> have re-imported the data, otherwise they slow things down.
>
> To convert your exported file from SQL_ASCII to UNICODE should be
> really easy too with realbasic, except that I'm not sure which
> encoding stands for SQL_ASCII.  If you only used English Roman
> characters, then you wouldn't even have to convert your file though
> and you could just upload it back into your new db.  If you need some
> sample code for the encodings conversion, just drop me a note, but
> it's just reading the file, converting, and writing it back out.
> However, if you have bytea data, you shouldn't apply this conversion
> on it and then you should export and import your data in 2 operations,
> keeping bytea separated.
>
> Cheers,
>
> Marc
>
> PS: information about pgSQL4RB, which is a quite new RAD tool to
> create PostgreSQL clients, can be found at
> <http://aliacta.com/pgsql4rb.htm>.  I know Brad uses it.
>
>> I have a few databases that set for ASCII encoding right now. Is
>> there a way to convert them to Unicode? I tried using "alter database
>> mConceptsEd set encoding='UNICODE' and about a hundred other minor
>> variations on that theme, but I kept the error message that encoding
>> wasn't a valid option. Am I stuck rebuilding the database from
>> scratch or can I do a conversion? Thanks in advance for any help you
>> can offer!
>>
>> --
>> brad@truetech.org
>> http://truetech.org
>> <><
>
>
A few months ago i was able to store and retrieve UNICODE data from an
ASCII-encoded DB. I think the only reason you may want to re-do the DB,
is the sorting, and queries. Unicode data in an ASCII-encoded DB might
not be sorted right. However, i haven't had the chance to play w/ it
more, so i don't know if it actually causes such problems or not. This
is just a guess, but a similar question from me earlier received a
warning not to use ASCII DBs for UNICODE. You can create a
UNICODE-encoded DB though in an ASCII-encoded initdb.

--
Sincerely,
Reshat.

-------------------------------------------------------------------------------------------
If you see my certificate with this message, you should be able to send me encrypted e-mail.
Please consult your e-mail client for details if you would like to do that.