Alphanumeric Sorting in PostgreSQL

Question

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.

Answer 1

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:

• Alphanumeric sorting with PostgreSQL
• Split comma separated column data into additional columns
• What is the impact of LC_CTYPE on a PostgreSQL database?

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.

Answer 2

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