Disclaimer

The views expressed on this blog are my own and do not necessarily reflect the views of Oracle.

Saturday, January 17, 2009

How to convert varying character type to integer in postgres

When you directly try to use 
alter table alter Type integer you will get exception in postgres.

So Using clause comes to rescue. It basically provides a mean (using a method)to convert the incoming data to appropriate type. So, you can try something as below:

CREATE OR REPLACE FUNCTION charToInt(charParam character varying)
  RETURNS integer AS
$BODY$
SELECT CASE WHEN trim($1) SIMILAR TO '[0-9]+' 
        THEN CAST(trim($1) AS integer) 
    ELSE NULL END;

$BODY$
  LANGUAGE 'sql' IMMUTABLE STRICT;

alter table (table name)
alter (column name of varying char type) TYPE integer  USING charToInt(column name of varying char type );

2 comments: