Postgres Pro
Downloads
Home   >   mailing lists

Thread: v7.4 pg_dump(all) need to encode from SQL_ASCII to UTF8

v7.4 pg_dump(all) need to encode from SQL_ASCII to UTF8

From
Ralph Smith
Date:
I'm looking at the v7.4 manuals and I don't see how to encode for
importing into a v8 DB using UTF8.

Maybe I'm making this hard on myself?
The old DB is using SQL_ASCII.
We'd like the new one to use UTF8.

As development proceeds, I'm going to have to do this regularly, both
the entire DB and by tables.
If not for the encoding, I've got all that down, even automated.

Thanks for any help!
Ralph

-------------------------------
p.s.  Isn't there a 16 bit Unicode for postgreSQL?

smithrn at u dot washington dot edu

Re: v7.4 pg_dump(all) need to encode from SQL_ASCII to UTF8

From
"Dean Gibson (DB Administrator)"
Date:
On 2008-02-22 17:57, Ralph Smith wrote:
> I'm looking at the v7.4 manuals and I don't see how to encode for
> importing into a v8 DB using UTF8.
>
> Maybe I'm making this hard on myself?
> The old DB is using SQL_ASCII.
> We'd like the new one to use UTF8.
>
> As development proceeds, I'm going to have to do this regularly, both
> the entire DB and by tables.
> If not for the encoding, I've got all that down, even automated.
>
> Thanks for any help!
> Ralph
>
> -------------------------------
> p.s.  Isn't there a 16 bit Unicode for postgreSQL?
>
> smithrn at u dot washington dot edu
>

There's nothing to do.  Dump the database in ASCII; create the new
database with a server_encoding of UTF-8; and import the data (which is
marked as having a client_encoding of SQL_ASCII).

ps: No.

--
Mail to my list address MUST be sent via the mailing list.
All other mail to my list address will bounce.

Re: v7.4 pg_dump(all) need to encode from SQL_ASCII to UTF8

From
Ralph Smith
Date:
I'm not sure if you're saying I should ignore these errors...

I'm using dumps from DB airaburst.


>postgres=# \l
         List of databases
    Name    |  Owner   | Encoding  
------------+----------+-----------
 airburst   | root     | SQL_ASCII
 lt_dev1    | postgres | UTF8
 lt_dev2    | postgres | UTF8
 lt_reports | postgres | UTF8
 postgres   | postgres | UTF8
 template0  | postgres | UTF8
 template1  | postgres | UTF8
(7 rows)

============================================
Looking at the dump of one table there...

--
-- PostgreSQL database dump
--

-- Started on 2008-02-25 12:28:48 PST

SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;

SET search_path = public, pg_catalog;

SET default_tablespace = '';

SET default_with_oids = false;

--
-- TOC entry 1455 (class 1259 OID 20378)
-- Dependencies: 1839 2
-- Name: board_posts; Type: TABLE; Schema: public; Owner: airburst; Tablespace: 

============================================
The error I get on trying to import it.


postgres@flexo:~/WORKING_DATA/airburst_tables$ psql lt_dev1 -f ./table_board_posts.sql 
SET
SET
SET
SET
SET
SET
SET
SET
CREATE TABLE
ALTER TABLE
psql:./table_board_posts.sql:248: ERROR:  invalid byte sequence for encoding "UTF8": 0x91
HINT:  This error can also happen if the byte sequence does not match the encoding expected by the server, which is controlled by "client_encoding".
CONTEXT:  COPY board_posts, line 8
CREATE INDEX

Thank you!


Re: v7.4 pg_dump(all) need to encode from SQL_ASCII to UTF8

From
Tom Lane
Date:
Ralph Smith <smithrn@washington.edu> writes:
> I'm not sure if you're saying I should ignore these errors...

No, not at all.

> I'm using dumps from DB airaburst.

Doesn't look like that --- you have

>      Name    |  Owner   | Encoding
> ------------+----------+-----------
>   airburst   | root     | SQL_ASCII

but the dump contains

> SET client_encoding = 'UTF8';

which indicates that it came from a database that claimed to have UTF8
encoding.  (Hmm .... although it's just barely possible that you have
PGCLIENTENCODING set in pg_dump's environment?)

> psql:./table_board_posts.sql:248: ERROR:  invalid byte sequence for
> encoding "UTF8": 0x91

In any case, this failure is pretty strong evidence that what is in the
dump is actually *not* UTF8 data, or at least not all of it is.  (I'd bet
on this particular value being in some LATINn encoding.)  What you're
going to need to do is figure out exactly what encoding the data really
has.  If you're lucky and it's all the same encoding, you can adjust it
to UTF8 by running the dump file through iconv, or just edit the SET
client_encoding command in the dump to match the true encoding (then
PG will take care of converting it to UTF8 during the load).

If you're not lucky, you have a mismash of differently encoded data,
and I'm afraid you're in for some unpleasant tedium getting it all into
one encoding.

The reason you're suffering this pain is that 7.x was not very good
about checking or enforcing encoding validity.  Current PG is much
stricter; cleaning up the data will cost you some pain now but it'll be
a good investment in the long run.

Alternatively, if you don't particularly *care* about encoding issues
and feel that everything was working fine before, you can create your
new DB with SQL_ASCII encoding (which actually means "no known
encoding") and PG will be just as lax as it was before.  But if you want
to say that the database uses UTF8 encoding, you need to present validly
encoded data.

            regards, tom lane