Solved

SQL Query ORDER BY not working as deisred

Posted on 2006-11-20
4
454 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 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

Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

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.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

632 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