On Fri, Sep 30, 2011 at 12:31 PM, Scott Marlowe <scott.marlowe@xxxxxxxxx> wrote:
Ah ok. The way I fix that is this:On Fri, Sep 30, 2011 at 12:46 PM, Cody Caughlan <toolbag@xxxxxxxxx> wrote:
> Please see below.
>
> On Fri, Sep 30, 2011 at 11:38 AM, Scott Marlowe <scott.marlowe@xxxxxxxxx>
> wrote:
>>
>> On Fri, Sep 30, 2011 at 12:26 PM, Cody Caughlan <toolbag@xxxxxxxxx> wrote:
>> > Thanks Scott. See below:
>> >
>> > On Fri, Sep 30, 2011 at 11:05 AM, Scott Marlowe
>> > <scott.marlowe@xxxxxxxxx>
>> > wrote:
>> >>
>> >> On Fri, Sep 30, 2011 at 11:25 AM, Cody Caughlan <toolbag@xxxxxxxxx>
>> >> wrote:
>> >> > I would like to change my server_encoding which is currently
>> >> > SQL_ASCII
>> >> > to UTF8.
>> >> >
>> >> > I have existing data that I would like to keep.
>> >> >
>> >> > From my understanding of the steps I need to:
>> >> >
>> >> > 1) alter the template1 database encoding via
>> >> >
>> >> > UPDATE pg_database SET encoding = 6 where datname IN ('template0',
>> >> > 'template1');
>> >>
>> >> Just create database using template0 as template and you can skip this
>> >> step ^^
>> >
>> >
>> > Wouldn't this only work if my template0 was UTF8 itself?
>> > => select datname, pg_encoding_to_char(encoding) from pg_database;
>> > datname | pg_encoding_to_char
>> > ----------------------+---------------------
>> > template1 | SQL_ASCII
>> > template0 | SQL_ASCII
>> > postgres | SQL_ASCII
>> >
>> > So it appears both template0 & template1 are SQL_ASCII, so how would
>> > creating from a new DB from template0 be any different than template1?
>>
>> Well, let's try, shall we? From a freshly created cluster on my
>> laptop, running 8.4:
>>
>> smarlowe=# select datname, pg_encoding_to_char(encoding) from pg_database;
>> datname | pg_encoding_to_char
>> -----------+---------------------
>> template1 | SQL_ASCII
>> template0 | SQL_ASCII
>> postgres | SQL_ASCII
>> smarlowe | SQL_ASCII
>> (4 rows)
>>
>> smarlowe=# create database j template template0 encoding 'UTF8';
>> CREATE DATABASE
>>
>> Seems to work.
>>
>> P.s. I'm not sure why it works, I just know that it does. :)
>>
>
> Ok, I see what you mean. This would create a new DB with the proper
> encoding. Which is "fine", and probably what I will do. I guess I see an
> ideal scenario being one where we permanently convert the template encoding
> to UTF8 so going forward I dont have to worry about forgetting to adding the
> encoding= 'UTF8' for every new DB I create.
update pg_database set datistemplate = false where datname='template1';
drop database template1;
create database template1 template template0 encoding 'UTF8';
But your way would likely work too.
Any idea waht the actual encoding of your source database is?
>> I think you got it backwards, the -f should be somthing other than
>> utf-8 right? That's what the -t should be right? Try iconv without a
>> -f switch and a -t of utf-8 and see what happens...
>
> You're right, I had -f when I needed -t. I tried it again with the same
> error:
> $ iconv -t utf-8 foo.sql > utf.sql
> iconv: illegal input sequence at position 2512661
SQL_ASCII is basically not really ascii, more like anything goes.
How would I find this? pg_database says my DB is SQL_ASCII.
"show all" says
client_encoding = SQL_ASCII
server_encoding = SQL_ASCII
