Re: BUG #1228: numeric field from a view from a view does not recognise any where values

On Tue, Aug 24, 2004 at 01:33:46PM -0300, PostgreSQL Bugs List wrote:

> CASE
> WHEN to_number(vw_rec_sum.code::text, '999'::text) >
> 194::numeric THEN 3
> WHEN to_number(vw_rec_sum.code::text, '999'::text) <
> 195::numeric AND to_number(vw_rec_sum.code::text, '999'::text) >
> 50::numeric THEN 2
> ELSE 1
> END AS period

> However whenever I try to query it with criteria on the period column I
> get SELECT * FROM vw_budget HAVING "period"='3';
> ERROR: invalid input syntax for type numeric: " "

Maybe there's a row somewhere in the vw_rec_sum relation that has a " "
value in the code field ... or a similar weirdness in some other column.
That's expected when you store numbers in text columns. Don't do that.

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Coge la flor que hoy nace alegre, ufana. ¿Quién sabe si nacera otra mañana?"