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

Answer 2

You can use the ASCII value of the ordered field:

SELECT column1 FROM table ORDER BY ascii(column1)

Special chars ASCII values are lower than letters ones.

Output

#num1
dir1 
dir2
dir3
t1

Answer 3

A brute force version to put # on top in the sort order

SELECT column1 
  FROM table1 
 ORDER BY CASE WHEN LEFT(column1, 1) = '#' 
               THEN 0 ELSE 1 END, column1

Here is SQLFiddle demo.

This may not be exactly what you want