From: Giuseppe Broccolo <giuseppe.broccolo@2ndquadrant.it>
To: pgsql-general@postgresql.org
Subject: Re: problem with query
Date: Fri, 13 Sep 2013 16:49:55 +0200
Message-ID: <52332613.2020904@2ndquadrant.it> (raw)
In-Reply-To: <CAApYZY8LOgsm8DSYCTJECYa4n4236DvrER6rfAgYX17mPZHGRQ@mail.gmail.com>
References: <CAApYZY8LOgsm8DSYCTJECYa4n4236DvrER6rfAgYX17mPZHGRQ@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-general>
Il 12/09/2013 22:34, Roberto Scattini ha scritto:
> hi, today we discovered that this query doesn't return the expected
> values:
>
> SELECT DISTINCT
> p.id <http://p.id;, p.apellido AS "Apellido", p.nombre AS "Nombre",
> pf.nombre AS "Funcion", to_char(da.f_ingreso_pg, 'dd/mm/yyyy') AS
> "Fecha Ingreso PG", e.estado AS "Estado", to_char(pe.fecha,
> 'dd/mm/yyyy') AS "Fecha Estado"
> FROM personal.personas AS p
> LEFT JOIN personal.personas_da_pg AS da ON p.id
> <http://p.id>=da.id_persona
> LEFT JOIN personal.personas_estado AS pe ON da.estado_id=pe.id
> <http://pe.id;
> LEFT JOIN personal.estados AS e ON pe.id_estado=e.id <http://e.id;
> LEFT JOIN procu_departamento AS d ON d.id
> <http://d.id>=da.id_departamento
> LEFT JOIN procu_departamento AS dto ON left(d.c_organigrama,
> 4)||'000'=dto.c_organigrama
> LEFT JOIN procu_departamento AS dir ON left(d.c_organigrama,
> 3)||'0000'=dir.c_organigrama
> LEFT JOIN procu_departamento AS dg ON left(d.c_organigrama,
> 2)||'00000'=dg.c_organigrama
> LEFT JOIN personal.funciones AS pf ON pf.id <http://pf.id>=da.funcion_id
> LEFT JOIN personal.profesiones AS pp ON pp.id
> <http://pp.id>=p.id_profesion
> WHERE p.apellido ilike '%nuñez%'
> ORDER BY "Apellido"
The problem is the encoding: SQL_ASCII encodes only the first 128
characters, so 'ñ' and 'Ñ' cannot be encoded in ASCII. If you insert
text 'ñ' or 'Ñ' in a table inside a database (e.g. 'ascii_test') with
SQL_ASCII encoding, they will be seen as two distinct characters:
ascii_test=# SELECT length(E'ñ');
length
--------
2
(1 row)
ascii_test=# SELECT length(E'Ñ');
length
--------
2
(1 row)
ascii_test=# SELECT 'ñ'::bytea;
bytea
--------
\xc3b1
(1 row)
ascii_test=# SELECT 'Ñ'::bytea;
bytea
--------
\xc391
(1 row)
Hexadecimals 'c3', 'b1' and '91' does not decode anything in ASCII, even
if terminal show pairs as 'ñ' or 'Ñ'. So ILIKE operator cannot apply
case insensitive pattern matching to data which does not encode any
string (in the SQL_ASCII encoding) and works as a normal LIKE.
Even if the client which insert data has 8-bit encoding (UTF8, where 'ñ'
and 'Ñ' are decoded), SQL_ASCII database cannot convert strings anyway,
and problem remains.
The best way is to work with a UTF8 encoded database. Is there a
particular reason to work with SQL_ASCII encoding?
Giuseppe.
--
Giuseppe Broccolo - 2ndQuadrant Italy
PostgreSQL Training, Services and Support
giuseppe.broccolo@2ndQuadrant.it | www.2ndQuadrant.it
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: giuseppe.broccolo@2ndquadrant.it
Subject: Re: problem with query
In-Reply-To: <52332613.2020904@2ndquadrant.it>
* 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