sql - PostgreSQL ORDER BY问题-自然排序

下表有一个PostgresORDER BY问题：

em_code  name
EM001    AAA
EM999    BBB
EM1000   CCC

要在表中插入新记录，
我用SELECT * FROM employees ORDER BY em_code DESC选择最后一条记录
使用reg exp从em_代码中提取字母并存储在ec_alpha
将重新生成的部分转换为整数ec_num
增加1ec_num++
再次使用足够的zeor和前缀ec_alpha的Pad
当em_code达到EM1000时，上述算法失败。
第一步将返回EM999而不是EM1000，它将再次生成EM1000作为新的em_code，从而打破唯一键约束。
知道如何选择EM1000吗？

最佳答案

原因是字符串按字母顺序排序（而不是像您希望的那样按数字排序），1在9之前排序。
你可以这样解决：

SELECT * FROM employees ORDER BY substring(em_code, 3)::int DESC

如果可以的话，从em_code中删除多余的“EM”，并保存一个整数作为开头，这样会更有效。
对评论中问题的补充回答
要从字符串中删除任何和所有非数字：

SELECT regexp_replace(em_code, E'\\D','','g')
FROM employees

\D是“非数字”的正则表达式class-shorthand。
'g'因为第四个参数是“全局”开关，用于将替换应用于字符串中的每个匹配项，而不仅仅是第一个匹配项。
所以我用从字符串中提取数字的空字符串替换每个非数字。