Convert a PostgreSQL column from text to numeric

You can alter the type of a column from text to numeric using the following syntax:

ALTER TABLE table ALTER COLUMN column TYPE numeric

However this will fail if there are any non-integer values in the column. In PostgreSQL 8.3+ this syntax can be extended as follows:

ALTER TABLE table ALTER COLUMN column TYPE numeric USING function(column);

So we can write a function that checks whether a string is entirely numeric, and if not, convert it to null:

CREATE OR REPLACE FUNCTION pc_chartoint(chartoconvert 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;

If we run the above code to create the function pc_chartoint(), then we can adapt our ALTER TABLE syntax as follows:

ALTER TABLE table ALTER COLUMN column TYPE integer USING pc_chartoint(column);

iShare Help

Convert a PostgreSQL column from text to numeric

Analytics

Related content