Thread: underscore pattern in a query doens't work
Hello everyone!
Below you can find the problem I'm dealing with.
I'd appreciate your help.
Thank you!!
- A description of what you are trying to achieve and what results you expect.:
I'd like to execute a query using the underscore as a pattern.
select id,etiqueta from limites_municipales where etiqueta like 'Garaf_a';
It should return some rows but it is not:
id | etiqueta
----+----------
(0 rows)
This is the content in the table:
palma=> select id,etiqueta from limites_municipales;
id | etiqueta
-------+--------------------------
0 | El Paso
12743 | Tazacorte
12744 | Los Llanos de Aridane
12745 | Villa de Mazo
12746 | Breña Baja
12747 | Santa Cruz de la Palma
12748 | Garafía
12749 | San Andrés y Sauces
12751 | Puntallana
12741 | Puntagorda
12742 | Tijarafe
12975 | Breña Alta
12976 | Fuencaliente de la Palma
12837 |
12846 |
.....
12910 |
12750 | Barlovento
(38 rows)
- PostgreSQL version number you are running:
PostgreSQL 8.4.1 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 3.4.6 20060404 (Red Hat 3.4.6-10), 64-bit
- How you installed PostgreSQL:
Downloaded from postgresql.org and installed.
- Changes made to the settings in the postgresql.conf file:
no changes.
- Operating system and version:
Linux centos
- What program you're using to connect to PostgreSQL:
psql
- Is there anything relevant or unusual in the PostgreSQL server logs?:
No
- For questions about any kind of error:
Does the behaviour has to do with the client_encoding or server_encoding of the cluster?
Both are set to SQL_ASCII.
The locale list in the server is:
LANG=es_ES.ISO-8859-15
LC_CTYPE="es_ES"
LC_NUMERIC="es_ES"
LC_TIME="es_ES"
LC_COLLATE="es_ES"
LC_MONETARY="es_ES"
LC_MESSAGES="es_ES"
LC_PAPER="es_ES"
LC_NAME="es_ES"
LC_ADDRESS="es_ES"
LC_TELEPHONE="es_ES"
LC_MEASUREMENT="es_ES"
LC_IDENTIFICATION="es_ES"
LC_ALL=es_ES
I'd like to execute a query using the underscore as a pattern.
select id,etiqueta from limites_municipales where etiqueta like 'Garaf_a';
It should return some rows but it is not:
id | etiqueta
----+----------
(0 rows)
This is the content in the table:
palma=> select id,etiqueta from limites_municipales;
id | etiqueta
-------+--------------------------
0 | El Paso
12743 | Tazacorte
12744 | Los Llanos de Aridane
12745 | Villa de Mazo
12746 | Breña Baja
12747 | Santa Cruz de la Palma
12748 | Garafía
12749 | San Andrés y Sauces
12751 | Puntallana
12741 | Puntagorda
12742 | Tijarafe
12975 | Breña Alta
12976 | Fuencaliente de la Palma
12837 |
12846 |
.....
12910 |
12750 | Barlovento
(38 rows)
- PostgreSQL version number you are running:
PostgreSQL 8.4.1 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 3.4.6 20060404 (Red Hat 3.4.6-10), 64-bit
- How you installed PostgreSQL:
Downloaded from postgresql.org and installed.
- Changes made to the settings in the postgresql.conf file:
no changes.
- Operating system and version:
Linux centos
- What program you're using to connect to PostgreSQL:
psql
- Is there anything relevant or unusual in the PostgreSQL server logs?:
No
- For questions about any kind of error:
Does the behaviour has to do with the client_encoding or server_encoding of the cluster?
Both are set to SQL_ASCII.
The locale list in the server is:
LANG=es_ES.ISO-8859-15
LC_CTYPE="es_ES"
LC_NUMERIC="es_ES"
LC_TIME="es_ES"
LC_COLLATE="es_ES"
LC_MONETARY="es_ES"
LC_MESSAGES="es_ES"
LC_PAPER="es_ES"
LC_NAME="es_ES"
LC_ADDRESS="es_ES"
LC_TELEPHONE="es_ES"
LC_MEASUREMENT="es_ES"
LC_IDENTIFICATION="es_ES"
LC_ALL=es_ES
"Sergio Calero." <angusyoung4@yahoo.es> writes:
> I'd like to execute a query using the underscore as a pattern.
> select id,etiqueta from limites_municipales where etiqueta like 'Garaf_a';
> [ but this fails to match 'Garaf�a' ]
I suspect what you have here is an encoding problem. That is, probably
the "�" is represented as a multi-byte character (most likely UTF8)
but the server thinks it's working with a single-byte encoding so that
any one character should be only one byte.
You didn't say what your encoding setup is, so it's hard to do more
than speculate.
> PostgreSQL 8.4.1 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 3.4.6 20060404 (Red Hat 3.4.6-10), 64-bit
You do realize this is about 3 years out of date? The 8.4 series is up
to release 8.4.13, and a lot of those updates contained fixes for
serious bugs.
regards, tom lane
Thanks Tom, for your suggestions.
We started the cluster up with this command:
./initdb -D /usr/local/postgre/data -E UTF8 -U sir
The rest of the variables related to encoding (locale) are:
lc_collate=C
lc_ctype=C
lc_messages=C
lc_monetary=C
lc_numeric=C
lc_time=C
Could you tell me which more info could I give you?
De: Tom Lane <tgl@sss.pgh.pa.us>
Para: Sergio Calero. <angusyoung4@yahoo.es>
CC: "pgsql-sql@postgresql.org" <pgsql-sql@postgresql.org>
Enviado: Jueves 13 de septiembre de 2012 16:19
Asunto: Re: [SQL] underscore pattern in a query doens't work
"Sergio Calero." <angusyoung4@yahoo.es> writes:
> I'd like to execute a query using the underscore as a pattern.
> select id,etiqueta from limites_municipales where etiqueta like 'Garaf_a';
> [ but this fails to match 'Garafía' ]
I suspect what you have here is an encoding problem. That is, probably
the "í" is represented as a multi-byte character (most likely UTF8)
but the server thinks it's working with a single-byte encoding so that
any one character should be only one byte.
You didn't say what your encoding setup is, so it's hard to do more
than speculate.
> PostgreSQL 8.4.1 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 3.4.6 20060404 (Red Hat 3.4.6-10), 64-bit
You do realize this is about 3 years out of date? The 8.4 series is up
to release 8.4.13, and a lot of those updates contained fixes for
serious bugs.
regards, tom lane
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
We started the cluster up with this command:
./initdb -D /usr/local/postgre/data -E UTF8 -U sir
The rest of the variables related to encoding (locale) are:
lc_collate=C
lc_ctype=C
lc_messages=C
lc_monetary=C
lc_numeric=C
lc_time=C
Could you tell me which more info could I give you?
De: Tom Lane <tgl@sss.pgh.pa.us>
Para: Sergio Calero. <angusyoung4@yahoo.es>
CC: "pgsql-sql@postgresql.org" <pgsql-sql@postgresql.org>
Enviado: Jueves 13 de septiembre de 2012 16:19
Asunto: Re: [SQL] underscore pattern in a query doens't work
"Sergio Calero." <angusyoung4@yahoo.es> writes:
> I'd like to execute a query using the underscore as a pattern.
> select id,etiqueta from limites_municipales where etiqueta like 'Garaf_a';
> [ but this fails to match 'Garafía' ]
I suspect what you have here is an encoding problem. That is, probably
the "í" is represented as a multi-byte character (most likely UTF8)
but the server thinks it's working with a single-byte encoding so that
any one character should be only one byte.
You didn't say what your encoding setup is, so it's hard to do more
than speculate.
> PostgreSQL 8.4.1 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 3.4.6 20060404 (Red Hat 3.4.6-10), 64-bit
You do realize this is about 3 years out of date? The 8.4 series is up
to release 8.4.13, and a lot of those updates contained fixes for
serious bugs.
regards, tom lane
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
"Sergio C." <angusyoung4@yahoo.es> writes:
> We started the cluster up with this command:
> ./initdb -D /usr/local/postgre/data -E UTF8 -U sir
That doesn't prove anything about the specific database where you're
having the problem ...
regards, tom lane