Well, I've finally sorted it out and can now convert text/varchar fields
into numeric I cannot BELIEVE I've had to resort to such things.
I've changed all my varchar fields to text and then applied this to them
(column name is 'litre_amount'):
translate (litre_amount, '.', '')::integer / case strpos(litre_amount, '.')
when 0 then 1 else (10^(char_length (litre_amount) - strpos(litre_amount,
'.'))) end
works for positive/negative and with/without decinal point.
You could equally do this straight from varchar I would imagine with:
translate (litre_amount, '.', '')::text::integer / case strpos(litre_amount,
'.') when 0 then 1 else (10^(char_length (litre_amount) -
strpos(litre_amount, '.'))) end
Would be pretty could is some could implement this in PostgreSQL natively
(and more quickly!). Why not let to_number and to_char work as in the Oracle
way and generically cast numerical fields back and forth into strings. This
kind of thing makes people give up at the first hurdle when they start
looking at products.
Cheers
Andy Marden