How to return records in correct order in PostgreS

问题:

Query

select 'T' union all select 'Z'  order by 1

returns

Z
T

but correct order should be

T
Z

Tried in 3 different OSes

• Windows 2003 server "PostgreSQL 9.1.3, compiled by Visual C++ build 1500, 32-bit"
• Windows 7 "PostgreSQL 9.2.1, compiled by Visual C++ build 1600, 32-bit"
• Debian "PostgreSQL 9.1.2 on x86_64-unknown-linux-gnu, compiled by gcc-4.4.real (Debian 4.4.5-8) 4.4.5, 64-bit"

Database settings are:

Collation:      et_EE.UTF-8 
Character type: et_EE.UTF-8

How to fix this?

Update

Databases were created using

CREATE DATABASE mydb WITH TEMPLATE=template0 OWNER=mydb_owner ENCODING='UNICODE'

OS locale was Estonian in all cases so database was created in estonian locale.

select 'Z' collate "et_EE" union all select 'S' collate "et_EE" order by 1

returns correct order:

S
Z

but

select 'Z' collate "et_EE" union all select 'T' collate "et_EE" order by 1

as stated in answer returns invalid order:

Z
T

回答1:

Nothing is wrong with original SQL.

Reason why it does not seem to work the way you expect it to because you use Estonian locale, but Estonian collation rules require that 'Z' comes before 'T'.

You can either use different collation or create database in en_US.UTF-8 locale.

回答2:

Nothing is wrong with the query.

A UNION [ALL] (or INTERSECT or EXCEPT) query is considered one query. ORDER BY applies to the union of all legs, not just the last one. You would have to use parenthesis to make the ORDER BY local to one leg of the query. Quoting the manual here:

select_statement UNION [ ALL | DISTINCT ] select_statement

select_statement is any SELECT statement without an ORDER BY, LIMIT, FOR UPDATE, or FOR SHARE clause. (ORDER BY and LIMIT can be attached to a subexpression if it is enclosed in parentheses. Without parentheses, these clauses will be taken to apply to the result of the UNION, not to its right-hand input expression.)

This just works (a sorts before b):

SELECT 'b'
UNION ALL
SELECT 'a'
ORDER BY 1

As does the original query in the question:

SELECT 'T' 
UNION ALL
SELECT 'Z'
ORDER BY 1

To check on your collation:

SHOW lc_collate;

et_EE.UTF-8 is the Estonian locale and Z comes before T in the Estonian alphabet. That's all.

Nothing is wrong with the collation.

回答3:

The problem is the collation:

select 'T' collate "et_EE.utf8" a union all select 'Z' collate "et_EE.utf8" order by 1;
 a 
---
 Z
 T

While with the en_US collation it works:

select 'T' collate "en_US.utf8" a union all select 'Z' collate "en_US.utf8" order by 1;
 a 
---
 T
 Z