JIRA: Change Custom Field Type

I created a custom field for a project with a custom field type of Number. It quickly became evident that this should have been a Text Field instead. Unfortunately, you can’t change this in JIRA (at least not without some convoluted custom field configuration scheming).

Direct SQL access to the rescue!

First, stop JIRA before access the database directly:

--This will change the field type to text
UPDATE customfield set customfieldtypekey = 'com.atlassian.jira.plugin.system.customfieldtypes:textfield',
customfieldsearcherkey = 'com.atlassian.jira.plugin.system.customfieldtypes:textsearcher'
where cfname = '<customfield_name>';
--Find all the possible values that need to be changed
select distinct numbervalue from customfieldvalue where customfield = (select id from customfield where cfname = '<customfield_name>');
--each of these values will be included in the case statement
update customfieldvalue SET stringvalue =
case
  when numbervalue = 1 then '1'
  when numbervalue = 2 then '2'
  when numbervalue = 3 then '3'
  else stringvalue
end
where customfield = (select id from customfield where cfname = '<customfield_name>');
--Now, remove the number values that were copied to string values
UPDATE customfieldvalue set numbervalue = null where customfield = (select id from customfield where cfname = '<customfield_name>');

Now, you can start JIRA and check that everything went as planned. Once you’ve confirmed the changes, you should reindex JIRA.

Advertisements

  1. Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: