支持大数的自然排序 [英] Natural sort supporting big numbers

问题描述

我有一些这样的数据：

 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

推荐答案

它的工作方式类似于@clemens。在复合类型中使用数字（= 十进制）：

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

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

db<>小提琴此处

我使用 int的原因引用的答案是性能。

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

这篇关于支持大数的自然排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！