postgresqlsortingnatural-sortbytea

Alphanumeric Sorting in PostgreSQL

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.

Solution

  • When sorting character data types, collation rules apply - unless you work with locale "C" which sorts 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. Split the string and sort the numeric part as number.

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

    Or, if all your numbers fit into bigint or even integer, use that instead (cheaper).

    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, or do it on-the-fly, cheaply.