Forum — Script to clean out old records

Re: Script to clean out old records

null@example.com (Alan Hale) — Thu, 20 May 2010 11:05:07 +0000

Ah!

1) I should have figured that for myself.

2) All works fine now - thanks!

All the best

Alan

Re: Script to clean out old records

null@example.com (johnvanbreda) — Thu, 20 May 2010 07:45:44 +0000

Hi Alan
1) Its not the database name, but the schema name you need to prefix. Assuming you used the default indicia schema during installation, the samples table is actually indicia.samples. To avoid having to specify it for each query, run the following first:

SET search_path=indicia, public;

.
2) Ah, er. Ahem, I seem to have copied in my script in the wrong order. Oops. I've edited the original post.

Best Wishes

Re: Script to clean out old records

null@example.com (Alan Hale) — Wed, 19 May 2010 14:07:23 +0000

John - a couple of issues:

1. In PgAdminIII I find I have to prefix the table names with the database name (even though the correct database is selcted in the main window);

2. More problematically, I get an error:

ERROR: update or delete on table "samples" violates foreign key constraint "fk_occurrence_sample" on table "occurrences"
DETAIL: Key (id) = (4) is still referenced from table "occurrences".

All the best

Alan

Re: Script to clean out old records

null@example.com (Alan Hale) — Sun, 16 May 2010 09:02:06 +0000

Thanks John - I have been meaning to ask you for something like this and it will definitely be useful.

Cheers

Alan

Re: Script to clean out old records

null@example.com (johnvanbreda) — Wed, 12 May 2010 08:07:05 +0000

In case it is of use to anyone (or me in the future!) here is a script that can be run against postgreSQL to remove records entered into a survey before a specified date:

delete from occurrence_attribute_values where id in (
select oav.id
from occurrence_attribute_values oav
inner join occurrences o on o.id=oav.occurrence_id
inner join samples s on s.id=o.sample_id
where s.survey_id=7 
and s.created_on < '2010-04-22');

delete from sample_attribute_values where id in (
select sav.id
from sample_attribute_values sav
inner join samples s on s.id=sav.sample_id
where s.survey_id=7 
and s.created_on < '2010-04-22');

delete from occurrences
where sample_id in
 (select id from samples where survey_id=7 
and created_on < '2010-04-22');

delete from samples where survey_id=7 
and created_on < '2010-04-22';