Delete tracker capture record from psql database

Hi there!

How can I delete tracker capture record from psql database.
Thanks a lot

Regards.
Patrick

2 Likes

Hi @pa_trick67,

If I may ask - why do you want to delete it from the database? It is recommended to delete it from the user interface because of the dependencies of the TEI in the system which might not be deleted directly from the database.

Best,
James.

1 Like

A post was split to a new topic: Deleting Traker Records using WEB API

@pa_trick67

If you want to delete a few TEIs, then use the UI - open the tracker dashboard for each case and use the red “delete case” icon in the upper right corner.

If you have a moderate number of cases to delete, consider using the web api (see separate thread)

If you have a LARGE number of tracker records to delete - like thousands, tens or even hundred of thousands - both the UI and the API will be painfully slow. Then consider using a set of pg delete statements like a modified version of those below (NB NB: Running the script as is will delete ALL tracker cases, so use carefully - how you modify them will depend on how you are marking tracker cases to be deleted):

Cleaning up ALL tracked entity instances data:

DELETE FROM messageconversation_usermessages;
DELETE FROM messageconversation_messages;
DELETE FROM programinstance_messageconversation;
DELETE FROM messageconversation;
DELETE FROM message;
DELETE FROM programinstance_outboundsms;
DELETE FROM trackedentitydatavalueaudit;
DELETE FROM trackedentitydatavalue;
DELETE FROM programstageinstancecomments;
DELETE FROM programinstancecomments;
DELETE FROM trackedentitycomment;
DELETE FROM trackedentityattributevalueaudit;
DELETE FROM trackedentityattributevalue;
DELETE FROM reservedvalue;
DELETE FROM programstageinstance;
DELETE FROM programinstance;
DELETE FROM relationship;
ALTER TABLE public.trackedentityinstance DROP CONSTRAINT IF EXISTS fk_trackedentityinstance_representativeid; – this constraint can be deleted permanently
ALTER TABLE public.programinstance DROP CONSTRAINT IF EXISTS fk_programinstance_trackedentityinstanceid;
DELETE FROM trackedentityinstance;
ALTER TABLE public.programinstance
ADD CONSTRAINT fk_programinstance_trackedentityinstanceid FOREIGN KEY (trackedentityinstanceid)
REFERENCES public.trackedentityinstance (trackedentityinstanceid) MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE NO ACTION;

Also remember to check for temporary tables and delete them!!

Regards
Calle

4 Likes