Hi, 2011/8/12 David Johnston <pol...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general