nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

How do I insert a record in customfieldvalue table?

Sorin Sbarnea (

In order to perform some complex offline migration I need to insert some custom field values directly inside the database but I just discovered that the ID column does not have auto-increment.

INSERT INTO customfieldvalue (customfield, issue, stringvalue) VALUES (10105, 10303 , '11');

What would be the appropiate way to do the insterts?

FYI, that's on PostgreSQL.

Accepted answers

All comments

Sorin Sbarnea (

Solved by using a line like this:

INSERT INTO customfieldvalue (id, customfield, issue, stringvalue) VALUES ( (select max(id)+1 from customfieldvalue), %s, %s , '%s')

Nic Brough -Adaptavist-

... and you remembered to do this with Jira offline, a proven backup, and re-indexed afterwards?

Sorin Sbarnea (

Obviously... anyway if you fail to do this you will wonder why nothing changes. Jira is caching almost everything so any change in the DB will not be visible without restart and reindex. Fail to do this and you'll feel the boumerang....

Nic Brough -Adaptavist-

Oh, and you haven't mentioned the increment you needed to do in the sequence table as well. Did you remember that?

S KHADHAR MOHAIDEEN

Should we increment the sequence table for each record updated? or after the bulk update, should we update the table with the max(id), ? Please confirm. Thanks.

Igor Bustiuc

Hi,

It is recommended to do this with Jira servides topped, but works also with Jira running.

1. UPDATE sequence_value_item SET seq_id = seq_id + 1 WHERE seq_name = 'CustomFieldValue'

2. wait for a second (sleep, dilay, whatever). This step is executed if Jira is up and running.

3. SELECT seq_id FROM sequence_value_item WHERE seq_name = 'CustomFieldValue' obtained value is used for column customfieldvalue.id;

4. INSERT INTO customfieldvalue (id, issue, customfield, textvalue) Values ({seq_id from point 3.}, {Id from jiraissue table}, {id from customfield table}, {the text value you wnat to save})

In table customfieldvalue there are many fields for value wanted to save, in order to know which column should use to store the value do a test from UI and after check where it is saved. So, use this column. In my case was column textvalue.

S KHADHAR MOHAIDEEN

Thank you Igor. Ofcourse yes I test in UAT, I keep Jira down, when I run the SQL queries.

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

jira

jira-administration