How to return records in correct order in PostgreSQL

Question

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

Answer 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.