The truth about boolean ordering in Postgres

boolean_ordering.txt

master=> SELECT * FROM (VALUES (1, true), (2, false), (3, null)) AS t (id,value);
 id | value 
----+-------
  1 | t
  2 | f
  3 | 
(3 rows)

master=> SELECT * FROM (VALUES (1, true), (2, false), (3, null)) AS t (id,value) order by value;
 id | value 
----+-------
  2 | f
  1 | t
  3 | 
(3 rows)

master=> SELECT * FROM (VALUES (1, true), (2, false), (3, null)) AS t (id,value) order by value asc;
 id | value 
----+-------
  2 | f
  1 | t
  3 | 
(3 rows)

master=> SELECT * FROM (VALUES (1, true), (2, false), (3, null)) AS t (id,value) order by value desc;
 id | value 
----+-------
  3 | 
  1 | t
  2 | f
(3 rows)

master=> SELECT * FROM (VALUES (1, true), (2, false), (3, null)) AS t (id,value) order by value desc nulls last;
 id | value 
----+-------
  1 | t
  2 | f
  3 | 
(3 rows)

# this one via https://stackoverflow.com/questions/2828120/sql-for-sorting-boolean-column-as-true-null-false/2828169#2828169
master=> SELECT * FROM (VALUES (1, true), (2, false), (3, null)) AS t (id,value) order by (case when value then 1 when value is null then 2 else 3 end);
 id | value 
----+-------
  1 | t
  3 | 
  2 | f
(3 rows)

master=> SELECT * FROM (VALUES (1, true), (2, false), (3, null)) AS t (id,value) order by (case when value then 1 when value is null then 2 else 3 end) desc;
 id | value 
----+-------
  2 | f
  3 | 
  1 | t
(3 rows)