Clever Geek Handbook

Postgres Convert string to number

I have a multimillion string table with a varchar field. Some of the varchar strings are floating point numbers. There is no limit to other fields that can completely isolate which strings are numbers and which are not. I want to create queries with ORDER BY on strings with a numeric value in a varchar field (ignoring non-numeric numbers). I just can't just call MyField :: numeric because it obfuscates non-numeric strings.

I thought of two main options:
(a) Use a regular expression to determine if a string is numeric.
(b) Catch casting exception and return zero for all non-numeric values.

Speed ​​is critical. I tried option (a) and it is very slow. I created a stored procedure to use a regex to test the value before the cast. Odd numbers are returned as null. I created an index using this stored procedure. The regex is so expensive. But I'm wondering if it's even worth catching the exception.

Is there an easy way to get MyField :: numeric return nulls for non-numeric data? Any suggestions to make it faster?

thank

+2


source to share


2 answers


Is the schema fixed or can you change it? In particular, is it possible to add another (null) column to store the floating point value, if any? Then the trigger for insert / update can ensure that the numeric column always has the correct value. This assumes you'll be asking more often than inserting / updating, of course.



+1


source


For speed, I would go with the solution to keep a separate column with a numeric type that is updated by the trigger. Zeros do not lose space. Otherwise, a solution with a stored procedure (or a case expression should suffice) that checks the value and then pronounces it correctly. Exceptional exceptions are likely to be the most expensive solution of all.



+1


source






More articles:


All Articles