Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
The truth about boolean ordering in Postgres
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)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.