Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.1k views
in Technique[技术] by (71.8m points)

postgresql - Order varchar string as numeric

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

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

It's absolutely possible.

ORDER BY varchar_column::int

Be sure to have valid integer literals in your varchar column or you get an exception. (Leading and trailing white space is ok - it will be 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?

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

ORDER BY NULLIF(regexp_replace(varchar_column, '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_column, 'D', '', 'g'), '') AS integer));

Then use the same expression in the ORDER BY clause:

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

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


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...