PostgreSQL ORDER BY issue – natural sort

by

Since Postgres 9.6, it is possible to specify a collation which will sort columns with numbers naturally.

https://www.postgresql.org/docs/10/collation.html

-- First create a collation with numeric sorting
CREATE COLLATION numeric (provider = icu, locale="en@colNumeric=yes");

-- Alter table to use the collation
ALTER TABLE "employees" ALTER COLUMN "em_code" type TEXT COLLATE numeric;

Now just query as you would otherwise.

SELECT * FROM employees ORDER BY em_code

On my data, I get results in this order (note that it also sorts foreign numerals):

Value
0
0001
001
1
06
6
13
۱۳
14

More Related Contents:

  1. Alphanumeric sorting with PostgreSQL
  2. Sort by column ASC, but NULL values first?
  3. Auto increment table column
  4. Any downsides of using data type “text” for storing strings?
  5. PostgreSQL DISTINCT ON with different ORDER BY
  6. ORDER BY the IN value list
  7. Humanized or natural number sorting of mixed word-and-number strings
  8. Optimize query with OFFSET on large table
  9. sql ORDER BY multiple values in specific order?
  10. How to concatenate columns in a Postgres SELECT?
  11. Time zone storage in data type “timestamp with time zone”
  12. Change PostgreSQL columns used in views
  13. Return multiple fields as a record in PostgreSQL with PL/pgSQL
  14. Sorting null values after all others, except special
  15. Casting NULL type when updating multiple rows
  16. Sort NULL values to the end of a table
  17. How much disk-space is needed to store a NULL value using postgresql DB?
  18. Why do NULL values come first when ordering DESC in a PostgreSQL query?
  19. How to store array or multiple values in one column
  20. Fetch records that are non zero after the decimal point in PostgreSQL
  21. Using an Alias column in the where clause in Postgresql
  22. How to use DISTINCT and ORDER BY in same SELECT statement?
  23. updating table rows in postgres using subquery
  24. Find overlapping date ranges in PostgreSQL
  25. How do you do date math that ignores the year?
  26. “Operator does not exist: integer =?” when using Postgres
  27. How to ORDER BY a SUM() in MySQL?
  28. PostgreSQL convert columns to rows? Transpose?
  29. Regular expression in PostgreSQL LIKE clause
  30. Is there a postgres CLOSEST operator?

Leave a Comment