From: Bosco Rama <postgres@boscorama.com>
To: Roberto Scattini <roberto.scattini@gmail.com>
Cc: pgsql <pgsql-general@postgresql.org>
Subject: Re: problem with query
Date: Thu, 12 Sep 2013 15:22:54 -0700
Message-ID: <52323EBE.7040003@boscorama.com> (raw)
In-Reply-To: <CAApYZY8K_yKreTQiN8CaQ585H_OXB7wfAOrNyCjqbnYaT1gTJg@mail.gmail.com>
References: <CAApYZY8LOgsm8DSYCTJECYa4n4236DvrER6rfAgYX17mPZHGRQ@mail.gmail.com>
<CADfwSsCu89SYEGLohutCrp0_k4h8qNHWXYiRPCR05w=_wODuoQ@mail.gmail.com>
<CAApYZY-R=c-gVi0fptQ-BS3FKStr7VLao97Ejd=E9vn+qWhj9Q@mail.gmail.com>
<CADfwSsCARi3JO-ZXXp13rp96Y6a2swfS+M9k-zRNVMujUdzZaQ@mail.gmail.com>
<523239EB.9010102@boscorama.com>
<CAApYZY8K_yKreTQiN8CaQ585H_OXB7wfAOrNyCjqbnYaT1gTJg@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-general>
On 09/12/13 15:07, Roberto Scattini wrote:
> hi bosco, in fact i believe that i have that problem... but i cant
> undestand why and how to fix it.
>
> the database has SQL_ASCII encoding, and my client... i am not sure, when i
> connect directly with psql from localhost i must set client encoding to
> SQL_ASCII or i receive an "invalid utf8 sequence" error...
>
> but the problem is there, from psql, from pgadmin and from php/apache (my
> webservers)
Well, the bytes that get encoded (in UTF8) for lowercase 'enye' is
x'c3b1' and for the uppercase version is x'c391' thus when they are
compared as straight bytes they will never be considered equal wrt
case-insensitivity.
The "invalid utf8 sequence" error is due to, as it says, an invalid
utf8 sequence in your text strings. This is due to the lack of any
enforcement provided by the SQL_ASCII encoding that was probably in
effect when the data was initially inserted.
To get to a point where you can use your data in its proper encoding
you will need to identify which strings are invalid and then dump
the DB and restore it into an appropriately initialized DB. You could
use the restore process to help you find the bad strings but that is
tedious as all hell. Better to scan you strings somehow and locate the
ones that are invalid and fix them prior to the dump.
HTH,
Bosco.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-general@postgresql.org
Cc: postgres@boscorama.com, roberto.scattini@gmail.com
Subject: Re: problem with query
In-Reply-To: <52323EBE.7040003@boscorama.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox