SQL Query ORDER BY not working as deisred

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.
LVL 8
mhuntsAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
earth man2Connect With a Mentor Commented:
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
 
earth man2Commented:
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
 
mhuntsAuthor Commented:
Is there a createdb switch? I don't want to have to initdb, as there are other databases I don't want impacted.
0
 
earth man2Commented:
You will have to change the default port to avoid clashes with other service.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.