2

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!

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 !!!

  • Thank you for your reply Ramesh, but what about the type bigint ? Is it understandable as the type int in CiviCRM ? – Vangelis Pantazis May 26 '16 at 10:09
  • I guess - you need change in the DB level from int to big int - manually – Ramesh - ARTECH Consultancy May 26 '16 at 10:59
0

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.

  • This is possibly better posted as a separate question, instead of an answer on another question but you will need to alter the data so it's all integers (removing comma's and dollar signs), otherwise mysql will issue a warning when you try and change the data type. – Luke Stewart Feb 6 at 18:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.