Alphanumeric Sorting in PostgreSQL

2019-07-14 17:44发布

I have this table with a character varying column in Postgres 9.6:

id | column 
------------
1  |IR ABC-1
2  |IR ABC-2
3  |IR ABC-10

I see some solutions typecasting the column as bytea.

select * from table order by column::bytea.

But it always results to:

id | column 
------------
1  |IR ABC-1
2  |IR ABC-10
3  |IR ABC-2

I don't know why '10' always comes before '2'. How do I sort this table, assuming the basis for ordering is the last whole number of the string, regardless of what the character before that number is.

2条回答
不美不萌又怎样
2楼-- · 2019-07-14 18:07

A possible problem with sorting character data types is that collation rules apply (unless you work with locale "C" (which simply defaults to sorting characters by there byte values). Applying collation rules may or may not be desirable. It makes sorting more expensive in any case. If you want to sort without collation rules, don't cast to bytea, use COLLATE "C" instead:

SELECT * FROM table ORDER BY column COLLATE "C";

However, this does not yet solve the problem with numbers in the string you mention. You have to split the string and sort the numeric part as number.

SELECT *
FROM   table
ORDER  BY split_part(column, '-', 2)::numeric;

Or, if all your number fit into bigint or even integer, use that instead.

I ignored the leading part because you write:

... the basis for ordering is the last whole number of the string, regardless of what the character before that number is.

Related:

Typically, it's best to save distinct parts of a string in separate columns as proper respective data types to avoid any such confusion.

And if the leading string is identical for all columns, consider just dropping the redundant noise. You can always use a VIEW to prepend a string for display.

查看更多
戒情不戒烟
3楼-- · 2019-07-14 18:30

As in the comments split and cast the integer part

select *
from
    table
    cross join lateral
    regexp_split_to_array(column, '-') r (a)
order by a[1], a[2]::integer
查看更多
登录 后发表回答