PostgreSQL – Sort Varchar by Numeric Fields

natural sortorder-bypostgresqlpostgresql-12

I'm sure this has been asked before, but since the question can be formulated in many different ways it's difficult to find the proper answer.

I have an orders table with a varchar field for the order number, which is formatted with 4-digits year, a dash (-), and a progressive numeric value. For example it may contain the following values:

SELECT number FROM orders ORDER BY number LIMIT 10;

  number  
----------
 1999-13
 2019-11
 2020-1
 2020-10
 2020-100
 2020-12
 2020-2
 2020-21
 2020-3
 2021-1

I need to sort that field by year and then by the progressive number, with this expected result:

  number  
----------
 1999-13
 2019-11
 2020-1
 2020-2
 2020-3
 2020-10
 2020-12
 2020-21
 2020-100
 2021-1

A DB fiddle is here.

My questions are:

  1. Which is the simplest way to achieve this with an ORDER subclause?
  2. How to add an efficient index using this custom sort without having to modify the table?

I would like to keep at least the first answer as database-agnostic as possible (that's why I haven't included db specific tags), but if different best answers are possible for different DBMS/versions, let's assume PostgreSQL 12.

Best Answer

I would convert that value to an integer array, then sort on that array:

SELECT number 
FROM orders 
ORDER BY string_to_array(number, '-')::int[] 
LIMIT 10;

Online example

Related Question