2

I have some data like this:

 id | templateName 
----+--------------
 10 | a
 61 | a
 63 | a
  4 | a
  6 | a
  7 | a
 34 | a
 35 | a
 62 | a
  1 | a
 13 | a
 25 | a
 26 | a
 66 | a
 68 | a
 70 | a
 65 | a
  5 | a1
 73 | a5
  3 | a15
  2 | a15a
 69 | a15b
 64 | a15b4
 74 | a15b21
  8 | a214748364

I'm doing natural sort with the following code:

CREATE TYPE ai AS (a text, i int);

select id, "templateName" from daily_templates
order by ARRAY(SELECT ROW(x[1], CASE x[2] WHEN '' THEN '0' ELSE x[2] END)::ai
               FROM regexp_matches("templateName", '(\D*)(\d*)', 'g')  x)
       , "templateName";

And it works well like I show above. Now I want to support large numbers such as 

a111111111111111111111

which will be out of range of integer. How can I do that? Reference: Humanized or natural number sorting of mixed word-and-number strings

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Nguyen Hoang Vu
  • 751
  • 2
  • 14
  • 35
  • 1
    If your numbers are not arbitrary large, you can use `BIGINT` (= 8 byte integers) instead of `INT` for `i` in `ai`. For very large numbers `DECIMAL`might be a solution. – clemens Aug 05 '19 at 04:07
  • I tried to create i bigint, but it still cast to integer, i think because of the regexp_matches – Nguyen Hoang Vu Aug 05 '19 at 04:11

2 Answers2

2

It works like @clemens suggested. Use numeric (= decimal) in the composite type:

CREATE TYPE ai AS (a text, i numeric);

db<>fiddle here

The reason I used int in the referenced answer is performance.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
1

Alternatively, if you have PostgreSQL v10 or better with support for ICU collations, you could

CREATE COLLATION natural (provider = icu, locale = 'en-US-u-kn-true');

and then sort like this:

... ORDER BY "templateName" COLLATE natural
Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263