Обсуждение: Problem in order by
Hi,
I make a dump from a database in cygwin in Windows to PG 7.3 in linux Red
Hat 9.
But I have a problem with 'order by'.
The query is 'select prodes from es002 where prodes like 'TELHA%' order by
1'
In the Cygwin, the return is correct:
prodes
----------------------------------------------------
TELHA FRANCESA CAMBORIU
TELHA PORTUGUESA SAO CRISTOVAO
TELHA ROMANA LEONEL PEREIRA
TELHA TRANSPARENTE FRANCESA
TELHA TRANSPARENTE PORTUGUESA
TELHA TRANSPARENTE ROMANA
TELHA ZINCO 1 1/2 P/POSTE TELHADO
TELHAO FRANCES SAO CRISTOVAO *
TELHAO ROMANO SAO CRISTOVAO-GOIVA *
But in Linux the return is:
prodes
----------------------------------------------------
TELHA FRANCESA CAMBORIU
TELHAO FRANCES SAO CRISTOVAO *
TELHAO ROMANO SAO CRISTOVAO-GOIVA *
TELHA PORTUGUESA SAO CRISTOVAO
TELHA ROMANA LEONEL PEREIRA
TELHA TRANSPARENTE FRANCESA
TELHA TRANSPARENTE PORTUGUESA
TELHA TRANSPARENTE ROMANA
TELHA ZINCO 1 1/2 P/POSTE TELHADO
I already executed vacuumbd and reindex database but nothing.
Somebody could help me?
Tks
Reuly Mendes
On Mon, 11 Apr 2005, [iso-8859-1] Reuly Bússolo Mendes wrote: > In the Cygwin, the return is correct: > > prodes > ---------------------------------------------------- > TELHA FRANCESA CAMBORIU > TELHA PORTUGUESA SAO CRISTOVAO > TELHA ROMANA LEONEL PEREIRA > TELHA TRANSPARENTE FRANCESA > TELHA TRANSPARENTE PORTUGUESA > TELHA TRANSPARENTE ROMANA > TELHA ZINCO 1 1/2 P/POSTE TELHADO > TELHAO FRANCES SAO CRISTOVAO * > TELHAO ROMANO SAO CRISTOVAO-GOIVA * > > > But in Linux the return is: > > prodes > ---------------------------------------------------- > TELHA FRANCESA CAMBORIU > TELHAO FRANCES SAO CRISTOVAO * > TELHAO ROMANO SAO CRISTOVAO-GOIVA * > TELHA PORTUGUESA SAO CRISTOVAO > TELHA ROMANA LEONEL PEREIRA > TELHA TRANSPARENTE FRANCESA > TELHA TRANSPARENTE PORTUGUESA > TELHA TRANSPARENTE ROMANA > TELHA ZINCO 1 1/2 P/POSTE TELHADO > > I already executed vacuumbd and reindex database but nothing. > > Somebody could help me? This probably is because you have a different collation set on the redhat box. If you are using something other than "C" locale, the ordering on the redhat box seems reasonable given that most locales ignore spaces for their first pass, so 'TELHAO' < 'TELHA P' for example. If you want byte ordering sorting, you can initdb in "C" locale.
Hi Stephan, tks I drop database and move directory data to data01. I already executed "initdb --locale=C" and the file postgresql.conf change to LC_MESSAGES, LC_MONETARY, LC_NUMERIC and LC_TIME = 'C', but the problem persist. I already executed too after move the directory data: initdb --encoding=LATIN1 --locale=C initdb --encoding=SQL_ASCII --locale=C Somebody have more any ideia? Tks ----- Original Message ----- From: "Stephan Szabo" <sszabo@megazone.bigpanda.com> To: "Reuly Bússolo Mendes" <reuly@terra.com.br> Cc: <pgsql-admin@postgresql.org> Sent: Monday, April 11, 2005 12:05 PM Subject: Re: [ADMIN] Problem in order by On Mon, 11 Apr 2005, [iso-8859-1] Reuly Bússolo Mendes wrote: > In the Cygwin, the return is correct: > > prodes > ---------------------------------------------------- > TELHA FRANCESA CAMBORIU > TELHA PORTUGUESA SAO CRISTOVAO > TELHA ROMANA LEONEL PEREIRA > TELHA TRANSPARENTE FRANCESA > TELHA TRANSPARENTE PORTUGUESA > TELHA TRANSPARENTE ROMANA > TELHA ZINCO 1 1/2 P/POSTE TELHADO > TELHAO FRANCES SAO CRISTOVAO * > TELHAO ROMANO SAO CRISTOVAO-GOIVA * > > > But in Linux the return is: > > prodes > ---------------------------------------------------- > TELHA FRANCESA CAMBORIU > TELHAO FRANCES SAO CRISTOVAO * > TELHAO ROMANO SAO CRISTOVAO-GOIVA * > TELHA PORTUGUESA SAO CRISTOVAO > TELHA ROMANA LEONEL PEREIRA > TELHA TRANSPARENTE FRANCESA > TELHA TRANSPARENTE PORTUGUESA > TELHA TRANSPARENTE ROMANA > TELHA ZINCO 1 1/2 P/POSTE TELHADO > > I already executed vacuumbd and reindex database but nothing. > > Somebody could help me? This probably is because you have a different collation set on the redhat box. If you are using something other than "C" locale, the ordering on the redhat box seems reasonable given that most locales ignore spaces for their first pass, so 'TELHAO' < 'TELHA P' for example. If you want byte ordering sorting, you can initdb in "C" locale. -- No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.9.6 - Release Date: 11/4/2005
On Mon, 11 Apr 2005, [iso-8859-1] Reuly Bússolo Mendes wrote: > Hi Stephan, tks > > I drop database and move directory data to data01. > I already executed "initdb --locale=C" and the file postgresql.conf change > to LC_MESSAGES, LC_MONETARY, LC_NUMERIC and LC_TIME = 'C', but the problem > persist. I think LC_COLLATE would be the one that is the issue, but --locale=C should have done it I think. To be sure, what does "show LC_COLLATE;" give on the database that has the problem?
The command's return teste=# show LC_COLLATE; ERROR: Option 'lc_collate' is not recognized I remade the process of yesterday (dropdb and move directory) and added in initdb the option --lc_collate=C without taking off -- locale=C, executed but it does not appear in postgresql.conf and the problem continues. it forgives me for my English Tks Reuly ----- Original Message ----- From: "Stephan Szabo" <sszabo@megazone.bigpanda.com> To: "Reuly Bússolo Mendes" <reuly@terra.com.br> Cc: <pgsql-admin@postgresql.org> Sent: Tuesday, April 12, 2005 10:58 AM Subject: Re: [ADMIN] Problem in order by On Mon, 11 Apr 2005, [iso-8859-1] Reuly Bússolo Mendes wrote: > Hi Stephan, tks > > I drop database and move directory data to data01. > I already executed "initdb --locale=C" and the file postgresql.conf change > to LC_MESSAGES, LC_MONETARY, LC_NUMERIC and LC_TIME = 'C', but the problem > persist. I think LC_COLLATE would be the one that is the issue, but --locale=C should have done it I think. To be sure, what does "show LC_COLLATE;" give on the database that has the problem? ---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend -- No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.9.7 - Release Date: 12/4/2005
On Tue, 12 Apr 2005, [iso-8859-1] Reuly Bússolo Mendes wrote: > The command's return > teste=# show LC_COLLATE; > ERROR: Option 'lc_collate' is not recognized Darn, that must have been added after 7.3.x. If you have pg_controldata, I think you can use pg_controldata <path to data directory>. I don't remember enough about 7.3 at this point to know for certain. > I remade the process of yesterday (dropdb and move directory) and added in > initdb the option --lc_collate=C without taking off -- locale=C, executed > but it does not appear in postgresql.conf and the problem continues. You are doing that with the server stopped, correct?
On Tue, 12 Apr 2005, Stephan Szabo wrote: > On Tue, 12 Apr 2005, [iso-8859-1] Reuly Bússolo Mendes wrote: > > > The command's return > > teste=# show LC_COLLATE; > > ERROR: Option 'lc_collate' is not recognized > > Darn, that must have been added after 7.3.x. If you have pg_controldata, > I think you can use pg_controldata <path to data directory>. I don't > remember enough about 7.3 at this point to know for certain. > > > I remade the process of yesterday (dropdb and move directory) and added in > > initdb the option --lc_collate=C without taking off -- locale=C, executed > > but it does not appear in postgresql.conf and the problem continues. > > You are doing that with the server stopped, correct? I mean the initdb.
Yes, the server is stopped.
I will go to verify on the pg_controldata!
Tks
----- Original Message -----
From: "Stephan Szabo" <sszabo@megazone.bigpanda.com>
To: "Reuly Bússolo Mendes" <reuly@terra.com.br>
Cc: <pgsql-admin@postgresql.org>
Sent: Tuesday, April 12, 2005 11:43 AM
Subject: Re: [ADMIN] Problem in order by
On Tue, 12 Apr 2005, Stephan Szabo wrote:
> On Tue, 12 Apr 2005, [iso-8859-1] Reuly Bússolo Mendes wrote:
>
> > The command's return
> > teste=# show LC_COLLATE;
> > ERROR: Option 'lc_collate' is not recognized
>
> Darn, that must have been added after 7.3.x. If you have pg_controldata,
> I think you can use pg_controldata <path to data directory>. I don't
> remember enough about 7.3 at this point to know for certain.
>
> > I remade the process of yesterday (dropdb and move directory) and added
in
> > initdb the option --lc_collate=C without taking off -- locale=C,
executed
> > but it does not appear in postgresql.conf and the problem continues.
>
> You are doing that with the server stopped, correct?
I mean the initdb.
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
--
No virus found in this incoming message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.9.7 - Release Date: 12/4/2005