Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Query ORDER BY not working as deisred

Posted on 2006-11-20
4
Medium Priority
?
455 Views
Last Modified: 2008-02-01
I have a  PostgreSQL database using the UTF8 character set. I have a field name, which has data such as

CHAN, ALFONSO
CHANG, ALBERT
CHANG, MICHAEL
CHAN, JACKIE

When I run a query

SELECT ...
ORDER BY name

I get the results in the order above. What I would like is data properly ordered as

CHAN, ALFONSO
CHAN, JACKIE
CHANG, ALBERT
CHANG, MICHAEL

Since the comma has a lower ASCII value than all upper and lower case alpha, it should sort the second way, but PostgreSQL isn't doing it. I know it's doing this because the comma is being ignored in the sort. I know I could split last name and first name into separate fields, but this doesn't solve other issues I have with other database fields, where the same problem manifests itself. So these are the constraints:

Must use UTF8 character set
Must sort the second way (above)
Cannot split the field into multiple fields
Can't do any work arounds like temp tables, etc.
I suppose there's a parameter setting that would allow this to sort properly, but I couldn't find it in the documentation.
0
Comment
Question by:mhunts
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
4 Comments
 
LVL 22

Expert Comment

by:earth man2
ID: 17983542
initdb --no-locale
testdb=> select * from x order by col1;
      col1      
----------------
 CHAN, ALFONSO
 CHAN, JACKIE
 CHANG, ALBERT
 CHANG, MICHAEL
(4 rows)

testdb=> show server_encoding;
 server_encoding
-----------------
 UTF8
(1 row)
0
 
LVL 8

Author Comment

by:mhunts
ID: 17984464
Is there a createdb switch? I don't want to have to initdb, as there are other databases I don't want impacted.
0
 
LVL 22

Accepted Solution

by:
earth man2 earned 2000 total points
ID: 17991235
see http://www.postgresql.org/docs/8.1/interactive/charset.html

"If you want the system to behave as if it had no locale support, use the special locale C or POSIX.

The nature of some locale categories is that their value has to be fixed for the lifetime of a database cluster. That is, once initdb has run, you cannot change them anymore. LC_COLLATE and LC_CTYPE are those categories. They affect the sort order of indexes, so they must be kept fixed, or indexes on text columns will become corrupt. PostgreSQL enforces this by recording the values of LC_COLLATE and LC_CTYPE that are seen by initdb. The server automatically adopts those two values when it is started."

What you could do is to run another postmaster instance.   Set PGDATA to point to a clean directory for the database data.
export PGDATA=/usr/local/data/db2
initdb --no-locale
pg_ctl -l /tmp/pg2.log start
0
 
LVL 22

Expert Comment

by:earth man2
ID: 17991260
You will have to change the default port to avoid clashes with other service.
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Many developers have database experience, but are new to PostgreSQL. It has some truly inspiring capabilities. I have several years' experience with Microsoft's SQL Server. When I began working with MySQL, I wanted a quick-reference to MySQL (htt…
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
Steps to create a PostgreSQL RDS instance in the Amazon cloud. We will cover some of the default settings and show how to connect to the instance once it is up and running.
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
Suggested Courses

670 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question