Solved

SQL Query ORDER BY not working as deisred

Posted on 2006-11-20
4
439 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
  • 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 500 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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Best database to use for Maps is PostgreSQL. This is an open source database. Comes as a package with most Linux OS. For more info visit the following site: http://www.postgresql.org/ (http://www.postgresql.org/) This requires some add-o…
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…
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.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

757 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now