Hi,
2011/8/12 David Johnston <polobo@yahoo.com>:
> In my table, some of the columns are in text datatype. Few data will come
> down from UI layer as integers. I want to convert that to string/text before
> saving it into the table. Please help me on this.
>
>
> SQL Standard: "CAST( value AS text )" [or varchar]
> PostgreSQL short-hand: "value::text"
>
> In both formats replace value with whatever you want to convert. When
> writing a parameterized query (using ?) you can write "?::text" ( or Cast(?
> AS type) ) to explicitly cast the unknown parameter. The "text" in the
> above can be any type name.
>
> David J.
You often find this advice of doing a cast.
But this only works if the input is a clean list of number characters already!
Anything other than this will issue an error:
postgres=# SELECT '10'::int;
After trying hard to cope with anything possibly as an input string I
found this:
postgres=# SELECT to_number('0'||mytextcolumn,
'99999999999.000')::int FROM mytable;
You can try this here: Show all peaks of Switzerland which are higher
than 4000 meters above sea.
SELECT ST_AsText(way) AS geom, name||','||ele AS label
FROM osm_point
WHERE "natural" = 'peak'
AND to_number('0'||ele, '99999999999.000')::int >= 4000
Any better solutions are welcome.
Yours, Stefan
