Delete Program in PostgreSQL

Support - Assistance technique
1

Dear All,

During our intial implementation, a few Tracker based programs were created and users allowed to enter data but later better desines were implemented and now need to delete the old program but due to association with other objects, these cant be deleted from the frontend, anyone with a script that can do the task from postgresql?

Thank you.

2

Hi there!
I have a starting pont for you. The script has not been used in a while, and it is only tested with my one program - so expect that there is a relation or two that needs to be added before everything works. Obviously you need to change the script to delete your program, and not the program named ‘Some program’ as it is currently:

delete from program_attributes where programtrackedentityattributeid in(select pa.programtrackedentityattributeid from program p join program_attributes pa on p.programid = pa.programid where p.name = 'Some program');

delete from program_organisationunits where programid in(select p.programid from program p where p.name = 'Some program');

update program set relatedprogramid = null where programid not in(select p.programid from program p where p.name = 'Some program');

delete from programstagedataelement where programstageid in(select ps.programstageid from program p join programstage ps on ps.programid = p.programid where p.name = 'Some program');

delete from programstagesection where programstageid in(select ps.programstageid from program p join programstage ps on ps.programid = p.programid where p.name = 'Some program');

delete from trackedentitydatavalue where programstageinstanceid in (select psi.programstageinstanceid from program p join programstage ps on ps.programid = p.programid join programstageinstance psi on ps.programstageid = psi.programstageid where p.name  = 'Some program');

delete from programstageinstancecomments where programstageinstanceid in (select psi.programstageinstanceid from program p join programstage ps on ps.programid = p.programid join programstageinstance psi on ps.programstageid = psi.programstageid where p.name = 'Some program');

delete from programstageinstance where programstageid in(select ps.programstageid from program p join programstage ps on ps.programid = p.programid where p.name = 'Some program');

delete from programstage where programid in(select p.programid from program p where p.name = 'Some program');

delete from program_userroles where programid in (select p.programid from program p where p.name = 'Some program');

delete from programinstance where programid in(select p.programid from program p where p.name = 'Some program');

delete from program where programid in(select p.programid from program p where p.name = 'Some program');

If you manage to make a more generic SQL tool, you are welcome to send a PR here: dhis2-utils/resources/sql at master · dhis2/dhis2-utils · GitHub

3 Likes
3

Thank you Markus, this is very helpful.

4

Hi @Markus Bekken ,

I added a few more tables to the query but I couldn’t easily figure out the joins on two tables commented out below. Please help.

Thanks

5

I apparently didnt save the edits so Ill have to redo it. The Tables were A number from the programrules and associated tables, Program sections tables, I think there was one from program attributes table and cant remember the other one.