Convert postgres field of type varchar to numeric

I have query to modify 2 million over records in postgres.

The problem is that one of the field is of type VARCHAR, and I need to do some mathematical operations on it. Right now I'm casting the field to numeric like this :-

CAST(attribute as numeric)

The whole query takes approximately 4 hours to run.

I am looking at ways to fasten then execution time. Are the any way I can change the field type from varchar to numeric first before I execute the que开发者_开发技巧ry? I can't use alter table alter column type to do this.

If you can't block the table for reading for such long time, there's not doubt that the best approuch is create a "second" table and do a subsequent update, like @OMG Ponies said.

Also, try to disable triggers if any of them will do something with new value (eg: log's trigger, since we are not changing the "value" it self). This can increase performance a lot, depending what your triggers do.

Something like this:

-- STEP 1: CREATING SECOND TABLE
START TRANSACTION;
CREATE TABLE MY_SECOND_TABLE AS SELECT <YOURKEYFIELDNAME>, (ATTRIBUTE AS NUMERIC) AS ATTRIBUTE FROM MY_TABLE;    
CREATE UNIQUE INDEX UI_MY_SECOND_TABLE ON MY_SECOND_TABLE (<YOURKEYFIELDNAME>);
COMMIT;

-- STEP 2: UPDATING A SOURCE TABLE
START TRANSACTION;
ALTER TABLE MY_TABLE DISABLE TRIGGER ALL;
ALTER TABLE MY_TABLE DROP COLUMN ATTRIBUTE;
ALTER TABLE ATTRIBUTE ADD ATTRIBUTE INTEGER;
UPDATE MY_TABLE T SET ATTRIBUTE = (SELECT ATTRIBUTE FROM MY_SECOND_TABLE T2 WHERE T2.<YOURKEYFIELDNAME> = T.<YOURKEYFIELDNAME>);    
ALTER TABLE MY_TABLE ENABLE TRIGGER ALL;
COMMIT;

-- DROP SECOND TABLE
DROP TABLE MY_SECOND_TABLE;