Change data TYPE of a column in a table

Change data TYPE of a column in a table - ALTER data type in a COLUMN in a table


alter table test_table alter column table_id TYPE bigint

Error :
column "table_id" cannot be cast to type "pg_catalog.int8"

Solution
-----------

ALTER TABLE sometable
ALTER COLUMN somecolumn TYPE new_data_type
USING some_function_call_to_cast(somecolumn);


Step 1 : you need to make a function to cast existing type to new data type
Step 2 : call alter USING the casting function

Below i have worked out one example
----------------------------------------

ALTER TABLE test_table
ALTER COLUMN table_id TYPE bigint
USING test_function_call_to_cast(table_id);

Here i have alter the column table_id from VARCHAR to BIGINT in test_table


body of the function :

-----------------------------------------------------------------

CREATE OR REPLACE FUNCTION
test_function_call_to_cast(VARCHAR) RETURNS bigint
AS
$BODY$
SELECT
CASE
WHEN trim($1) SIMILAR TO '[0-9]+'
THEN CAST(trim($1) AS bigint)
ELSE NULL
END;

$BODY$
LANGUAGE 'sql' IMMUTABLE STRICT;

--------------------------------------------------------------------------

important

Even if the above did work, you've got some stuff in there you don't really care about - letters and so forth or an empty string. So you want to control how the cast is done anyway

in the above function
which takes a string value and if it looks like a number, it converts it to a number otherwise it just returns NULL: