Posted about 5 years ago. Visible to the public.

PostgreSQL: Ordering, NULLs, and indexes

When using ORDER BY "column" in PostgreSQL, NULL values will come last.

When using ORDER BY "column" DESC, NULLs will come first. This is often not useful.

Luckily, you can tell PostgeSQL where you want your NULLs, by saying

Copy
... ORDER BY "column" DESC NULLS LAST ... ORDER BY "column" ASC NULLS FIRST

Your indexes will have to specify this as well. In Rails, declare them using

Copy
add_index :table, :column, order: { column: 'DESC NULLS LAST' }

Multiple columns

When sorting by multiple columns, you can do the same thing:

Copy
SELECT * FROM "posts" ORDER BY "modified_at" DESC NULLS LAST, "title";

PostgreSQL will only use your index, if it includes all columns, and all columns are order correctly (or if all columns are ordered reversely). So, possible indexes are:

Copy
add_index :posts, [:modified_at, :title], order: { modified_at: 'DESC NULLS LAST' }

and

Copy
add_index :posts, [:modified_at, :title], order: { modified_at: 'ASC NULLS FIRST', :title => 'DESC' }

makandra has been working exclusively with Ruby on Rails since 2007. Our laser focus on a single technology has made us a leader in this space.

Owner of this card:

Avatar
Tobias Kraze
Last edit:
about 5 years ago
by Tobias Kraze
About this deck:
We are makandra and do test-driven, agile Ruby on Rails software development.
License for source code
Posted by Tobias Kraze to makandra dev
This website uses short-lived cookies to improve usability.
Accept or learn more