By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,809 Members | 1,997 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,809 IT Pros & Developers. It's quick & easy.

Is this "order by" logic right

P: n/a
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.

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 12 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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"
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 12 '05 #2

P: n/a
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.

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 12 '05 #3

P: n/a
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.
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 12 '05 #4

P: n/a
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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.