I migrated one of our trackers from a host that used sqlite to store 
tracker data to a new host that uses postgres. We have a number of 
keywords for issues. Some of those keywords we like to have at the top of 
the list. To accomplish this, those keywords were prepended with the '*' 
character. For example, '*Monthly Maintenance'. That worked fine in the 
old host. In the new host it ignores the '*' and which causes the '*' 
keywords to not appear at the top of the list. I tried several other 
special characters to no avail.

I suspect that the problem is with postgres because the following query

select * from _keyword where __retired__=0 order by _name;

ignores the '*' character prepended to some keyword names. In sqlite the 
'*' keywords are ordered at the top of the result set.

I haven't checked if sqlite and/or postgres obey locale settings (or
implement
something similar), but with "sort" it differs very much:

$ echo -e 'a\nA\n*a\n0a\n*A\n0A\nb\nB\n*b\n0b\n*B\n0B'|LC_COLLATE=POSIX sort
*A
*B
*a
*b
0A
0B
0a
0b
A
B
a
b

$ echo -e
'a\nA\n*a\n0a\n*A\n0A\nb\nB\n*b\n0b\n*B\n0B'|LC_COLLATE=en_US.utf-8
sort
0a
0A
0b
0B
a
*a
A
*A
b
*b
B
*B

$ echo -e
'a\nA\n*a\n0a\n*A\n0A\nb\nB\n*b\n0b\n*B\n0B'|LC_COLLATE=de_DE.utf-8
sort
0a
0A
0b
0B
a
A
*a
*A
b
B
*b
*B

Maybe just prepend "000_" or something like that?

http://www.postgresql.org/docs/9.3/static/locale.html#AEN33113

The locale settings influence the following SQL features:
- Sort order in queries using ORDER BY or the standard comparison
operators on textual data

...

Check that PostgreSQL is actually using the locale that you think it is.
The LC_COLLATE and LC_CTYPE settings are determined when a database is
created, and cannot be changed except by creating a new database.

So I assume this is not a bug in roundup or sqlite, but standard behaviour.

OK to close this?

In psql:
it=> show lc_collate;
 lc_collate
-------------
 en_US.UTF-8

An alternative I got from a google search is to do

SELECT _name from _keyword WHERE __retired__=0 ORDER BY convert_to(_name, 
'SQL_ASCII');

or

SELECT _name from _keyword WHERE __retired__=0 ORDER BY convert_to(_name, 
'latin1');

Another not-so-good alternative is to add an _order column to the _keyword 
class and do the sort manually.

I ended putting digits in front of the 3 keywords I wanted at the top:

 1 Monthly Maintenance
 2 Routine Maintenance
 3 Troubleshooting
 ATAF
 App Development
...

A possible fix is to do something similar to what I did for issue 2550805. 
Change the 'order by' code in Class._filter_sql in 
backends/rdbms_common.py (near line 2265) and add a special case for 
backends/back_postgresql.py. I'm not sure what the Postgres special case 
would be that would correctly handle encodings for other languages. Doing 
a convert_to using SQL_ASCII or latin1 is definitely not correct.

Please mark this issue as closed.

Thank you for the reminder to close it, I would not have remembered to
do it!