Error duplicate key violates unique constraint after to import data to Database

Support - Assistance technique
1

Hi,

We have needed to import data directly from Dhis Database(postgres), but we have found that when entering the application does not allow adding new records related to the imported information.

example:

If i import patient data, then after the application not allow adding new patients.

when we doing tracking we found that the cause is an error that occurs because DHIS try to create record with an existing key “Error Violates duplicate unique key constraint”.

we have tried to index the tables again and did not solve the problem, what other solution could be applied?

Thanks,

Helder Castrillon

Secretaria Departamental de salud del Cauca

Colombia

···

–

@Heldersoft

2

Hi Helder,

Your problem is caused because you have not used the Postgres hibernate_sequence to generate IDs, but rather generated your own IDs. There are 3 answers:

  1. This situation is avoided by using the DXF format, which takes a bit more work - then DHIS2 will add the IDs

  2. If you are instead injecting data directly via SQL, it is recommended to use the following pattern (example from an earlier post by Jason to this list):

INSERT INTO organisationunit(organisationunitid, name, shortname,

parentid, active,openingdate)
VALUES(nextval(‘hibernate_sequence’::regclass),'ad Federal Muslim

Women Association in Nigeria Adamawa (FOMWAN
A)',‘adFederalMuslimWomenAssociationinNigeriaAdamawaF’,501,‘true’,‘2001-01-01’);

Notice instead of setting the organisationunitid, i use the nextval of the hibernate_sequence. Of course the injection becomes a bit more complex, as you have to be sure that you get all of the parentIDs correct, but if you do it organisation layer by organisation layer, then it is relatively easy.

  1. However, you have not followed 1) or 2) and therefore have to resolve the issue. You will now have to identify the maximum ID used in the tables you have added to and then to set the sequence value

to one greater than the maximum.

To find out the current status of your sequence, run this:

SELECT nextval(‘hibernate_sequence’);

For example, if you have just added lots of orgunits manually with external IDs, you should run this:

SELECT max(organisationunitid)+1 AS id FROM organisationunit

Then use the new value like this:

SELECT setval(‘hibernate_sequence’, )

···

On Wed, Jun 19, 2013 at 12:49 AM, Helder Yesid Castrillon Cobo heldersoft@gmail.com wrote:

Hi,

We have needed to import data directly from Dhis Database(postgres), but we have found that when entering the application does not allow adding new records related to the imported information.

example:

If i import patient data, then after the application not allow adding new patients.

when we doing tracking we found that the cause is an error that occurs because DHIS try to create record with an existing key “Error Violates duplicate unique key constraint”.

we have tried to index the tables again and did not solve the problem, what other solution could be applied?

Thanks,

Helder Castrillon

Secretaria Departamental de salud del Cauca

Colombia

–

@Heldersoft

Mailing list: https://launchpad.net/~dhis2-users

Post to : dhis2-users@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-users

More help : https://help.launchpad.net/ListHelp

–
Knut Staring

Dept. of Informatics, University of Oslo

+4791880522

http://dhis2.org

3

Hi again,

Since you have been importing patients, you probably want to find the max like this:

SELECT max(patientid)+1 AS id FROM patient

···

On Wed, Jun 19, 2013 at 7:01 AM, Knut Staring knutst@gmail.com wrote:

Hi Helder,

Your problem is caused because you have not used the Postgres hibernate_sequence to generate IDs, but rather generated your own IDs. There are 3 answers:

  1. This situation is avoided by using the DXF format, which takes a bit more work - then DHIS2 will add the IDs
  1. If you are instead injecting data directly via SQL, it is recommended to use the following pattern (example from an earlier post by Jason to this list):

INSERT INTO organisationunit(organisationunitid, name, shortname,

parentid, active,openingdate)
VALUES(nextval(‘hibernate_sequence’::regclass),'ad Federal Muslim

Women Association in Nigeria Adamawa (FOMWAN
A)',‘adFederalMuslimWomenAssociationinNigeriaAdamawaF’,501,‘true’,‘2001-01-01’);

Notice instead of setting the organisationunitid, i use the nextval of the hibernate_sequence. Of course the injection becomes a bit more complex, as you have to be sure that you get all of the parentIDs correct, but if you do it organisation layer by organisation layer, then it is relatively easy.

  1. However, you have not followed 1) or 2) and therefore have to resolve the issue. You will now have to identify the maximum ID used in the tables you have added to and then to set the sequence value

to one greater than the maximum.

To find out the current status of your sequence, run this:

SELECT nextval(‘hibernate_sequence’);

For example, if you have just added lots of orgunits manually with external IDs, you should run this:

SELECT max(organisationunitid)+1 AS id FROM organisationunit

Then use the new value like this:

SELECT setval(‘hibernate_sequence’, )

–
Knut Staring

Dept. of Informatics, University of Oslo

+4791880522

http://dhis2.org

On Wed, Jun 19, 2013 at 12:49 AM, Helder Yesid Castrillon Cobo heldersoft@gmail.com wrote:

Hi,

We have needed to import data directly from Dhis Database(postgres), but we have found that when entering the application does not allow adding new records related to the imported information.

example:

If i import patient data, then after the application not allow adding new patients.

when we doing tracking we found that the cause is an error that occurs because DHIS try to create record with an existing key “Error Violates duplicate unique key constraint”.

we have tried to index the tables again and did not solve the problem, what other solution could be applied?

Thanks,

Helder Castrillon

Secretaria Departamental de salud del Cauca

Colombia

–

@Heldersoft

Mailing list: https://launchpad.net/~dhis2-users

Post to : dhis2-users@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-users

More help : https://help.launchpad.net/ListHelp

Knut Staring

Dept. of Informatics, University of Oslo

+4791880522

http://dhis2.org

–

4

thanks Knut,

the problem has been solved. Was first necessary to identify the table that had the highest index.

Regards

···

2013/6/19 Knut Staring knutst@gmail.com

Hi again,

Since you have been importing patients, you probably want to find the max like this:

SELECT max(patientid)+1 AS id FROM patient

–

@Heldersoft

On Wed, Jun 19, 2013 at 7:01 AM, Knut Staring knutst@gmail.com wrote:

Hi Helder,

Your problem is caused because you have not used the Postgres hibernate_sequence to generate IDs, but rather generated your own IDs. There are 3 answers:

  1. This situation is avoided by using the DXF format, which takes a bit more work - then DHIS2 will add the IDs
  1. If you are instead injecting data directly via SQL, it is recommended to use the following pattern (example from an earlier post by Jason to this list):

INSERT INTO organisationunit(organisationunitid, name, shortname,

parentid, active,openingdate)
VALUES(nextval(‘hibernate_sequence’::regclass),'ad Federal Muslim

Women Association in Nigeria Adamawa (FOMWAN
A)',‘adFederalMuslimWomenAssociationinNigeriaAdamawaF’,501,‘true’,‘2001-01-01’);

Notice instead of setting the organisationunitid, i use the nextval of the hibernate_sequence. Of course the injection becomes a bit more complex, as you have to be sure that you get all of the parentIDs correct, but if you do it organisation layer by organisation layer, then it is relatively easy.

  1. However, you have not followed 1) or 2) and therefore have to resolve the issue. You will now have to identify the maximum ID used in the tables you have added to and then to set the sequence value

to one greater than the maximum.

To find out the current status of your sequence, run this:

SELECT nextval(‘hibernate_sequence’);

For example, if you have just added lots of orgunits manually with external IDs, you should run this:

SELECT max(organisationunitid)+1 AS id FROM organisationunit

Then use the new value like this:

SELECT setval(‘hibernate_sequence’, )

–
Knut Staring

Dept. of Informatics, University of Oslo

+4791880522

http://dhis2.org

On Wed, Jun 19, 2013 at 12:49 AM, Helder Yesid Castrillon Cobo heldersoft@gmail.com wrote:

Hi,

We have needed to import data directly from Dhis Database(postgres), but we have found that when entering the application does not allow adding new records related to the imported information.

example:

If i import patient data, then after the application not allow adding new patients.

when we doing tracking we found that the cause is an error that occurs because DHIS try to create record with an existing key “Error Violates duplicate unique key constraint”.

we have tried to index the tables again and did not solve the problem, what other solution could be applied?

Thanks,

Helder Castrillon

Secretaria Departamental de salud del Cauca

Colombia

–

@Heldersoft

Mailing list: https://launchpad.net/~dhis2-users

Post to : dhis2-users@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-users

More help : https://help.launchpad.net/ListHelp

Knut Staring

Dept. of Informatics, University of Oslo

+4791880522

http://dhis2.org

–