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.

Have a look at the existing [postgresql] [natural-sort] questions. — mu is too short
– mu is too short, Commented Apr 3, 2017 at 3:09
If you want to sort by the name, and then subsort by the number, you'll have to separate the two entities into two columns, and then order by the two columns. — user707650
– user707650, Commented Apr 3, 2017 at 3:16
@evert, i think i can get somewhere with your idea.. but how do i split the column string? — muffin
– muffin, Commented Apr 3, 2017 at 3:34
If column is always in that format you have given in example, you can simply do split_part(column, '-', 2)::integer to get integer out of it. Otherwise regexp might be better. — Łukasz Kamiński
– Łukasz Kamiński, Commented Apr 3, 2017 at 7:06

Erwin Brandstetter · Accepted Answer · 2021-04-08 13:21:07Z

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.

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.

Clodoaldo Neto · Accepted Answer · 2017-04-03 12:28:39Z

1

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

answered Apr 3, 2017 at 12:28

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

Collectives™ on Stack Overflow

Alphanumeric Sorting in PostgreSQL

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related