Order varchar string as numeric

Question

Is it possible to order result rows by a varchar column cast to integer in Postgres 8.3?

Solleret

Answer 1

It's absolutely possible.

ORDER BY varchar_col::int

Be sure to have valid integer literals in your varchar column for each entry or you get an exception invalid input syntax for integer. Leading and trailing white space is ok - that's trimmed automatically.

If that's the case, though, then why not convert the column to integer to begin with? Smaller, faster, cleaner, simpler.

How to avoid exceptions?

Postgres 16 or newer

If the input might not be valid integer literals, use the dedicated function pg_input_is_valid() to test without raising an exception:

ORDER BY CASE WHEN pg_input_is_valid(varchar_col, 'integer') THEN varchar_col::int END

SQL CASE defaults to null in the absence of ELSE - which sorts last in default ascending order. See:

• Sort by column ASC, but NULL values first?

The new built-in function is faster and more reliable than below manual solution. Also different in that it does not try to fix broken input.

Postgres 15 or older

To remove non-digit characters before the cast and thereby avoid possible exceptions:

ORDER BY NULLIF(regexp_replace(varchar_col, '\D', '', 'g'), '')::int

• The regexp_replace() expression effectively removes all non-digits, so only digits remain or an empty string. (See below.)

• \D is shorthand for the character class [^[:digit:]], meaning all non-digits ([^0-9]).
  In old Postgres versions with the outdated setting standard_conforming_strings = off, you have to use Posix escape string syntax E'\\D' to escape the backslash \. This was default in Postgres 8.3, so you'll need that for your outdated version.

• The 4th parameter g is for "globally", instructing to replace all occurrences, not just the first.

• You may want to allow a leading dash (-) for negative numbers.

• If the the string has no digits at all, the result is an empty string which is not valid for a cast to integer. Convert empty strings to NULL with NULLIF. (You might consider 0 instead.)

The result is guaranteed to be valid. This procedure is for a cast to integer as requested in the body of the question, not for numeric as the title mentions.

How to make it fast?

One way is an index on an expression.

CREATE INDEX tbl_varchar_col2int_idx ON tbl
(cast(NULLIF(regexp_replace(varchar_col, '\D', '', 'g'), '') AS integer));

Then use the same expression in the ORDER BY clause:

ORDER BY
cast(NULLIF(regexp_replace(varchar_col, '\D', '', 'g'), '') AS integer)

Test with EXPLAIN ANALYZE whether the functional index actually gets used.

Bugloss

Any way to avoid that exception? :) – Solleret

@Jauzsika: you will have to define first what the result should be for non-integer values. Sort last? Or delete any non-digit characters before the cast? – Bugloss

Thanks for the exception part. That regexp looks slow, am I right? – Solleret

@Jauzsika: Definitely slower than the plain cast, but not that much. The fact that you have to compute the values at all is the major slow-down here. If you want fast, either convert the column in the table to integer, or add a redundant column that holds the pre-computed integer. Then you can index the column, which will make it a lot faster (provided the query is simple and the index can be used for the sort). Or use an index on an expression. I amended my answer. – Bugloss

Thanks for the exhausting training :)). – Solleret

EPIC COMMENT +1 !! Just one append : E'\\D' not worked for me, produced this error > "org.postgresql.util.PSQLException: ERROR: type "ee" does not exist", then I put just '\D' and worked like a charm. I'm using postgresql9.3 version . thanks again! – Benue

Wow! +1 for an awesome answer. The index is amazing. I had no idea you could do such magic. – Headspring

Answer 2

Also in case you want to order by a text column that has something convertible to float, then this does it:

select * 
from your_table
order by cast(your_text_column as double precision) desc;

Kinky