Read more

PostgreSQL: Ordering, NULLs, and indexes

Tobias Kraze
July 03, 2015Software engineer at makandra GmbH

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

Illustration online protection

Rails Long Term Support

Rails LTS provides security patches for old versions of Ruby on Rails (2.3, 3.2, 4.2 and 5.2)

  • Prevents you from data breaches and liability risks
  • Upgrade at your own pace
  • Works with modern Rubies
Read more Show archive.org snapshot

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

... 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

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

Multiple columns

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

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:

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

and

add_index :posts, [:modified_at, :title], order: { modified_at: 'ASC NULLS FIRST', :title => 'DESC' }
Posted by Tobias Kraze to makandra dev (2015-07-03 14:10)