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!

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

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match