CODEWITHSUNDEEP
optimizationpostgresql
User1
User1

Reputation: 41203

Postgres Convert String to Number

I have a multi-million row table with a varchar field. Some of the rows of the varchar are floating point numbers. There is no constraint on other fields that can completely isolate which rows are numbers and which are not. I want to create queries with an ORDER BY on the rows with the numeric value in the varchar field (ignoring non-numerics). I can't just simply call MyField::numeric because it pukes on the non-numeric rows.

I thought of two basic options:
(a) Use a regex to determine if the row is numeric.
(b) Catch the cast exception and return a null for all non-numeric values.

Speed is crucial. I tried option (a) and it is painfully slow. I created a stored procedure to use a regex to check the value before the cast. Non-numerics return as null. I created an index using that stored procedure. The regex is so expensive. But I'm wondering if catching an exception is even worth the effort.

Is there a simple way to have MyField::numeric return nulls for non-numeric data? Any suggestions to make this run faster?

Thanks

Upvotes: 2

Views: 4040

Answers (2)

Peter Eisentraut
Peter Eisentraut

Reputation: 36729

For speed, I would go with the solution of keeping a separate column with a numeric type that is updated by a trigger. Nulls don't waste any space. Otherwise, the solution with a stored procedure (or a case expression should be enough) that checks the value and then casts it sounds right. Catching exceptions will probably be the most expensive solution of them all.

Upvotes: 1

Jon Skeet
Jon Skeet

Reputation: 1500805

Is the schema fixed, or can you change it? In particular could you add another (nullable) column to store the floating point value, if any? Then a trigger for insert/update could make sure that the numeric column always has the right value. This assumes that you're going to be querying more often than inserting/updating of course.

Upvotes: 1

Related Questions