Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL Query ORDER BY not working as deisred

Posted on 2006-11-20
4
Medium Priority
?
457 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 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

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.
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Suggested Courses

783 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