Обсуждение: Is this "order by" logic right
Hello list, The query has the following in the where: UPPER(cty_Name) LIKE 'NEW%' ORDER BY cty_name it returns the data in this way: Newark Newark New Britain Newburgh New Cannan New Castle Newcomerstown The table has index on cty_name (btree), I'll appreciate any comments or suggestions about it. Thanks -- Josué Maldonado.
On Wed, 24 Sep 2003, [ISO-8859-1] Josué Maldonado wrote: > Hello list, > > The query has the following in the where: > > UPPER(cty_Name) LIKE 'NEW%' ORDER BY cty_name > > it returns the data in this way: > > Newark > Newark > New Britain > Newburgh > New Cannan > New Castle > Newcomerstown > > The table has index on cty_name (btree), I'll appreciate any comments or > suggestions about it. You haven't given your locale or system type, but this seems reasonably if you're in a locale that generally ignores spaces (for example my redhat machine does so in en_US). If you want byte order sorting, you'll probably need to re-initdb with LC_COLLATE set to "C"
Hi Stephan, Stephan Szabo wrote: > On Wed, 24 Sep 2003, [ISO-8859-1] Josué Maldonado wrote: > You haven't given your locale or system type, but this seems reasonably > if you're in a locale that generally ignores spaces (for example my redhat > machine does so in en_US). If you want byte order sorting, you'll > probably need to re-initdb with LC_COLLATE set to "C" The db was initialized with the defaults in postgresql.conf, so the locale is en_US. Any idea how to change it? -- Josué Maldonado.
On Wed, 24 Sep 2003, [ISO-8859-1] Josué Maldonado wrote: > Hi Stephan, > > Stephan Szabo wrote: > > > On Wed, 24 Sep 2003, [ISO-8859-1] Josué Maldonado wrote: > > You haven't given your locale or system type, but this seems reasonably > > if you're in a locale that generally ignores spaces (for example my redhat > > machine does so in en_US). If you want byte order sorting, you'll > > probably need to re-initdb with LC_COLLATE set to "C" > > The db was initialized with the defaults in postgresql.conf, so the > locale is en_US. Any idea how to change it? Unfortunately, the only real way to change the collation right now is to re-initdb with LC_COLLATE set to something else in the environment. The index order for btree indexes is dependant on the collation order so changing it after the fact would be bad. This does mean dumping and reloading or some kind of replication to get the data between the old system and new.
What should we comment on? 翽 wrote: > Hello list, > > The query has the following in the where: > > UPPER(cty_Name) LIKE 'NEW%' ORDER BY cty_name > > it returns the data in this way: > > Newark > Newark > New Britain > Newburgh > New Cannan > New Castle > Newcomerstown > > The table has index on cty_name (btree), I'll appreciate any comments > or suggestions about it. > > Thanks >