Re: tsearch2 in postgresql 8.3.1 - invalid byte sequence for encoding "UTF8": 0xc3

patrick wrote:
> SELECT 'abc'::text || 'def'::text;
> it's working fine (no need to convert the query ASCII to UTF8 or such

OK, now try each of these in turn:
UPDATE product SET search_vector = to_tsvector(name);

UPDATE product SET search_vector = setweight(to_tsvector(name), 'A');

UPDATE product SET search_vector = setweight(to_tsvector(name), 'A') ||
to_tsvector(description);

If they work when you're typing them in pgadmin but not in the script
file then it's the script file. I copied your code and got a similar
error, except my table was empty. No data. So I knew it wasn't
complaining about the data itself.

> i am using pgadmin (1.8.2) to pass the query:
> show client_encoding = UNICODE.
>
> in postgresql.conf i have:
> client_encoding; Value = UTF8, Current value = UNICODE;
> i tried to restart postgresql for reloading his configuration, but it's
> still UNICODE...

The issue is what characters were in your script file.

> reinstalling postgresql 8.3.1 on windows and leaving the server encoding
> and client encoding to default (cannot remember what was it - something
> like WIN1252 or such) then i have no more problem, but i am worry about
> the impact of transfering the database to linux (eventually i would like
> to transfer my database to my linux server)...

There are three things to remember:
1. Is my database encoding supported by all my backends (and there you
are fine).
2. Can PG translate from my client-encoding to the database encoding
(European-based stuff should be fine).
3. Am I sending data in the client-encoding I've told PG I'm using.

I still think it's #3 that's causing you problems.

--
   Richard Huxton
   Archonet Ltd