tags:

views:

1204

answers:

2
+1  Q: 

Postgres Convert String to Number

+1  A: 

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.

Jon Skeet  2009-09-12 14:35:08
Yeah, I thought about that. Would that be a lot of wasted space for the nulls? I was also thinking of a table with a OneToOne relation that would be populated on a trigger. There are criteria I could use on other fields to tell if a value will never be numeric.
User1  2009-09-12 16:02:57
I don't know how Postgres stores nulls... you should probably read up on the exact table structure or just experiment.
Jon Skeet  2009-09-12 16:05:57
Storing a numeric datatype seems to have worked. I think it's worth the extra storage. Partial indexes the most. Now my queries went from 7 minutes to 20 seconds..not bad.
User1  2009-09-14 14:15:03
+1  A: 

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.

Peter Eisentraut  2009-09-14 10:38:21

related questions

How do I (or can I) SELECT DISTINCT on multiple columns (postgresql)?
Is it possible to make a recursive SQL query ?
What does it mean when a PostgreSQL process is "idle in transaction"?
C# .NET + PostgreSQL
Possible to perform cross-database queries with postgres?
Cascading deletes in PostgreSQL
How to concatenate strings of a string field in a PostgreSQL 'group by' query?
Languages other than SQL in postgres
How to prevent Write Ahead Logging on just one table in PostgreSQL?
Using MS Access & ODBC to connect to a remote PostgreSQL
Reasons for SQL differences
PostgreSQL perfomance monitoring tool
How do you use script variables in PostgreSQL?
MySQL vs PostgreSQL for Web Applications
joining latest of various usermetadata tags to user rows
Good strategy for leaving an audit trail/change history for DB applications?
PostgreSQL: GIN or GiST indexes?
Migrating from MySQL to PostgreSQL
What's the preferred way to connect to a postgresql database from PHP?
Use for the phppgadmin Reports Database?
Recommendation for a PostgreSQL Programming Tutorial?
SQL Case Statement Syntax?
What language do you use for Postgresql triggers and stored procedures?
String literals and escape characters in postgresql
Python and MySQL