Sort by column ASC, but NULL values first?

by

Postgres has the NULLS FIRST | LAST modifiers for ORDER BY expression:

... ORDER BY last_updated NULLS FIRST

The typical use case is with descending sort order (DESC), which produces the complete inversion of the default ascending order (ASC) with null values first – which is often not desirable. To sort NULL values last:

... ORDER BY last_updated DESC NULLS LAST

To support the query with an index, make it match:

CREATE INDEX foo_idx ON tbl (last_updated DESC NULLS LAST);

Postgres can read btree indexes backwards, but for some query plans it matters where NULL values are appended. See:

More Related Contents:

  1. Sorting null values after all others, except special
  2. Sort NULL values to the end of a table
  3. Why do NULL values come first when ordering DESC in a PostgreSQL query?
  4. Select rows which are not present in other table
  5. PostgreSQL DISTINCT ON with different ORDER BY
  6. ORDER BY the IN value list
  7. Create unique constraint with null columns
  8. Optimize query with OFFSET on large table
  9. sql ORDER BY multiple values in specific order?
  10. Best way to check for “empty or null value”
  11. PostgreSQL ORDER BY issue – natural sort
  12. Alphanumeric sorting with PostgreSQL
  13. Combine two columns and add into one new column
  14. IN Clause with NULL or IS NULL
  15. Check if NULL exists in Postgres array
  16. Optimize GROUP BY query to retrieve latest row per user
  17. How to speed up insertion performance in PostgreSQL
  18. PostgreSQL GROUP BY different from MySQL?
  19. How can I import a JSON file into PostgreSQL?
  20. Return multiple columns of the same row as JSON array of objects
  21. Normalize array subscripts so they start with 1
  22. Is there a way to define a named constant in a PostgreSQL query?
  23. Slow simple update query on PostgreSQL database with 3 million rows
  24. Return setof record (virtual table) from function
  25. postgres – where in (list) – column does not exist
  26. incomplete information from query on pg_views [duplicate]
  27. PostgreSQL: Give all permissions to a user on a PostgreSQL database
  28. Referencing current row in FILTER clause of window function
  29. PostgreSQL nested INSERTs / WITHs for foreign key insertions
  30. Fetch records that are non zero after the decimal point in PostgreSQL

Leave a Comment