Postgres Alter table to convert column type from char to bigint

Question

Possible Duplicate:
how to change column datatype from character to numeric in postgresql 8.4

If I have a field of type varchar (and all the values are null or string representations of numbers) how do I use alter table to convert this column type to bigint?

Answer 1

To convert simply by parsing the string (casting):

alter table the_table alter column the_column type bigint using the_column::bigint

In fact, you can use any expression in terms of the_column instead of the_column::bigint to customise the conversion.

Note this will rewrite the table, locking out even readers until it's done.

Answer 2

You could create a temporary column of type bigint, and then execute SQL like

UPDATE my_table SET bigint_column=varchar_column::bigint;

Then drop your varchar_column and rename bigint_column. This is kinda roundabout, but will not require a custom cast in postgres.

Answer 3

How to convert a string column type to numeric or bigint in postgresql

Design your own custom cast from string to bigint. Something like this:

CREATE OR REPLACE FUNCTION convert_to_bigint(v_input text)
RETURNS BIGINT AS $$
DECLARE v_bigint_value BIGINT DEFAULT NULL;
BEGIN
    BEGIN
        v_bigint_value := v_input::BIGINT;
    EXCEPTION WHEN OTHERS THEN
        RAISE NOTICE 'Invalid bigint value: "%".  Returning something else.', v_input;
        RETURN 0;
    END;
RETURN v_bigint_value;
END;

Then create a new table fixed_table_with_bigint with the same parameters as the old table except change the string column into the bigint column.

Then insert all the rows from the previous table (using the custom cast convert_to_integer ) into the new table:

insert into fixed_table_with_bigint
select mycolumn1, 
       convert_to_bigint(your_string_bigint_column),
       mycolumn3
    from incorrect_table

You may have to modify convert_to_bigint in order to handle strings which are not numbers, blankstrings, nulls, control characters and other Weirdness.

Then delete the first table and rename the 2nd table as the first table.