PostgreSQL: Ordering, NULLs, and indexes
When using ORDER BY "column"
in PostgreSQL, NULL
values will come last.
When using ORDER BY "column" DESC
, NULL
s will come first. This is often not useful.
Luckily, you can tell PostgeSQL where you want your NULL
s, 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
Copyadd_index :table, :column, order: { column: 'DESC NULLS LAST' }
Multiple columns
When sorting by multiple columns, you can do the same thing:
CopySELECT * 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:
Copyadd_index :posts, [:modified_at, :title], order: { modified_at: 'DESC NULLS LAST' }
and
Copyadd_index :posts, [:modified_at, :title], order: { modified_at: 'ASC NULLS FIRST', :title => 'DESC' }
Your development team has a full backlog of feature requests, chores and refactoring coupled with deadlines? We are familiar with that. With our "DevOps as a Service" offering, we support developer teams with infrastructure and operations expertise.