Re: Database Encoding

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(at)truetech(dot)org
>http://truetech.org
><><
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly

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(at)truetech(dot)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.