Change of custom field type to bigint

Question

I have a custom group where one of its custom fields is of type varchar(64). This field is storing huge numbers only (something that I did not know during development) so I want to convert it into a int type, a bigint type due to the really lengthy numbers it's storing My question is the following: Assuming that the length is the same in all stored entries (around 2,5 million entries, 12 or 16 digit length), can I convert it into another type simply by ALTER TABLE or I need to inform Civi that this is an int/bigint field? Lastly, since I haven't seen the use of Bigint, is it safe to assume that it won't cause havoc in Civi's functionality if I do so?

Thanks!

Answer 1

You have all the control of your custom table but always it is not advised to change custom field type in the database

Better way of doing this would be

In DB - Create a temp table of the same and move all the records to the temp table first

In the front end - delete the column with varchar and create a new one with int with the same name

In DB - move all the value from temp table to the custom field

I guess this helps !!!

Answer 2

I have similiar, though not exact, situation... my issue is VarChar field type with field values;

Field_name=estimated_household_income_56 Field_type=varchar(60)

As result of "varchar" field type, the field values do not enable "correct" indexing or "range" searching. In fact, the Ascending Index of potential values results as follows;

$1,000,000 $1,000,001 $100,000 $150,000 $200,000 $25,000 $250,000 $50,000 $500,000 $75,000

(these field values were already modified by me, '$0 to $24,999' SET to '$25,000' - using SQL UPDATE/SET/WHERE regime)

I know this will be cleaned up by changing to INT field type. IS it as simple as simply changing field type? OR, should I first, execute SQL commands to modify field values ELIMINATING the '$' and ',' from the field values, then change to INT.

My gut says get rid of the '$' and ',', but hoping this is a single click operation. Of course, did an SQL export of the table as backup.