How can I sort the postgres column with certain special characters?

Question

I am trying to sort on a character column in a Postgres database:

Select column1 from table order by column1

Output

dir1 
dir2
dir3
#num1
t1

I want the sort to print #num1 first the way sqlite does. Any ideas what I need to change in my query?

Answer 1

A possible solution would be to "disable" your collation setting for this sort:

WITH x(a) AS (VALUES
  ('dir1')
 ,('dir2')
 ,('dir3')
 ,('#num1')
 ,('t1')
 )
SELECT *
FROM   x
ORDER  BY a COLLATE "C";

Ad-hoc Collation for individual expressions requires PostgreSQL 9.1 or later.

Most locales would ignore the leading # for sorting. If you switch to "C", characters are effectively sorted by their byte values. This may or may not be what you want, though.

Many related questions, like here:
PostgreSQL UTF-8 binary collation