How to convert text field to numeric field and null non-numeric values?

Question

I have a column of text data which contains a bunch of various values...many of which are numbers, but others which are not.

I want to convert this text column to a numeric column and convert any values to numbers that can be done so successfully, but NULL out any text values that cannot be converted.

Is this possible in a single alter table statement?

alter table schema_1."tbl_265" alter column field_7 type numeric

Answer 1

I don't think that you can do this in a single statement. Postgres will try to cast each value in the column to numeric, and raise an error if that cannot be done, which will abort the whole operation.

Instead you could:

• create a new column with numeric datatype

• fill it with converted values - for this, the cleaner approach would be to create an ad-hoc conversion function

• drop the old string column

• rename the new numeric column

Here is a demo

-- sample table
create table mytable(id int primary key, col text);
insert into mytable(id, col) values
    (1, '123.5'), 
    (2, 'abc'), 
    (3, null)
;


-- conversion function
create or replace function cast_to_numeric(v_input text)
returns numeric as $$
declare v_output numeric default null;
begin
    begin
        v_output := v_input::numeric;
        exception when others then return null;
    end;
return v_output;
end;
$$ language plpgsql;

-- add a new column and fill it with converted values
alter table mytable add column newcol numeric;
update mytable set newcol = cast_to_numeric(col);

-- swap the columns
alter table mytable drop column col;
alter table mytable rename column newcol to col;

-- check the results
select * from mytable

id |   col
-: | ----:
 1 | 123.5
 2 |  null
 3 |  null