CODEWITHSUNDEEP
javapostgresql
mddev
mddev

Reputation: 79

postgresql order by desc for string not working

Using postgresql on a mac, I'm trying to order names in descending but I have noticed if the two names have the same first letter it does not check the second letter when trying to order.

For example suppose you have the following strings:

Amen, Alia, Atis,...etc

They should ideally be order as:

Alia, Amen, Atis,..etc

But postgresql only checks the first letter and doesn't seem to check the entire string. Therefore the order of names returned by postgresql is: 

Atis, Alia, Amen,..etc

or in just some random order. I have set the database collation to en_us.utf8.

The simplified query I'm trying to use is:

SELECT name 
FROM properties 
ORDER BY name;

How can I make postgresql order by the entire string, is that possible?

Update: When I try the query in pgadmin III, I find that it produces the same results, I think it may have something to do with the configuration of postgreSQL.

My configs of db are: collation and character type are: en_CA.UTF-8 tablespace: pg_default

Is there something wrong with the configuration I have set up on a mac?

Upvotes: 1

Views: 2896

Answers (2)

hruske
hruske

Reputation: 2243

My guess is you are experiencing issues with collation and/or non-printable characters.

You can try different collations as per http://www.postgresql.org/docs/9.1/static/collation.html with:

SELECT name COLLATE "C", name::bytea FROM properties 
LEFT OUTER JOIN observations on properties.id =observations.id order by 1 desc;

and you will be able to both experiment with collation (1st col) and see what exactly (hex binary value) is stored in name (2nd col).

Edit: After finding this Postgres.app issue, I'm fairly confident you've hit a bug in OSX collation support. To confirm, you can try sorting the same text in console using sort.

I don't know if a workaround exists at this moment, but the PGDG is aware of the problem and if all goes well, PostgreSQL 9.6 might solve your problem.

Upvotes: 2

Tomasz Jakub Rup
Tomasz Jakub Rup

Reputation: 10680

Natural order sort it's what You looking for.

PSQL functions

CREATE FUNCTION btrsort_nextunit(text) RETURNS text AS $$
SELECT CASE WHEN $1 ~ '[^0-9]+' THEN
COALESCE( SUBSTR( $1, LENGTH(SUBSTRING($1 FROM '[^0-9]+'))+1 ),
'' )
ELSE
COALESCE( SUBSTR( $1, LENGTH(SUBSTRING($1 FROM '[0-9]+'))+1 ),
'' )
END
$$ LANGUAGE SQL;

CREATE FUNCTION btrsort(text, integer) RETURNS text AS $$
SELECT CASE WHEN $2-1>0
THEN
RPAD(SUBSTR(COALESCE(SUBSTRING($1 FROM '^[^0-9]+'), ''), 1, 12), 12, ' ') ||
LPAD(SUBSTR(COALESCE(SUBSTRING(btrsort_nextunit($1) FROM '^[0-9]+'), ''), 1, 12), 12, 0) || btrsort(btrsort_nextunit(btrsort_nextunit($1)), $2-1)
ELSE
RPAD(SUBSTR(COALESCE(SUBSTRING($1 FROM '^[^0-9]+'), ''), 1, 12), 12, ' ') ||
LPAD(SUBSTR(COALESCE(SUBSTRING(btrsort_nextunit($1) FROM '^[0-9]+'), ''), 1, 12), 12, 0)
END
;

$$ LANGUAGE SQL;

CREATE FUNCTION btrsort(text) RETURNS text AS $$
SELECT btrsort($1, 10);
$$ LANGUAGE SQL;

Usage

SELECT room_number
FROM (SELECT room_number, btrsort(room_number) AS room_sort
FROM rooms
ORDER BY room_sort) AS foo

Result

room_number
-----------
8
9
9a
10

Links

http://www.postgresql.org/docs/8.0/interactive/queries-order.html http://2kan.tumblr.com/post/361326656/postgres-natural-ordering

Upvotes: 0

Related Questions