rough approximation of statements used to clean up bety200

cleanbety200.sql

begin;
delete from sites where id < 200000000001 and sitename not like '%Season 10%' and id != 1160 and id != 6000000001;
commit;
delete from benchmarks_benchmarks_reference_runs
delete from benchmarks_ensembles_scores
delete from benchmarks_ensembles
delete from reference_runs;
delete from benchmarks;
delete from inputs;
delete from pfts_species where id < 200000000001 
delete from entities where id < 200000000001 
delete from sitegroups_sites where id < 200000000001 
delete from 

delete from ensembles;
select count(*) from ensembles;


select * from species where scientificname = 'Sorghum bicolor'

select * from species where commonname like '%lettuce%'
select scientificname from species where id in (select distinct specie_id from cultivars)

delete from cultivars where id < 200000000001

select * from cultivars where specie_id =2760;
INSERT INTO "public"."species"("id", "spcd", "genus", "species", "scientificname", "commonname", "notes", "created_at", "updated_at", "AcceptedSymbol", "SynonymSymbol", "Symbol", "PLANTS_Floristic_Area", "State", "Category", "Family", "FamilySymbol", "FamilyCommonName", "xOrder", "SubClass", "Class", "SubDivision", "Division", "SuperDivision", "SubKingdom", "Kingdom", "ITIS_TSN", "Duration", "GrowthHabit", "NativeStatus", "NationalWetlandIndicatorStatus", "RegionalWetlandIndicatorStatus", "ActiveGrowthPeriod", "AfterHarvestRegrowthRate", "Bloat", "C2N_Ratio", "CoppicePotential", "FallConspicuous", "FireResistance", "FoliageTexture", "GrowthForm", "GrowthRate", "MaxHeight20Yrs", "MatureHeight", "KnownAllelopath", "LeafRetention", "Lifespan", "LowGrowingGrass", "NitrogenFixation", "ResproutAbility", "AdaptedCoarseSoils", "AdaptedMediumSoils", "AdaptedFineSoils", "AnaerobicTolerance", "CaCO3Tolerance", "ColdStratification", "DroughtTolerance", "FertilityRequirement", "FireTolerance", "MinFrostFreeDays", "HedgeTolerance", "MoistureUse", "pH_Minimum", "pH_Maximum", "Min_PlantingDensity", "Max_PlantingDensity", "Precipitation_Minimum", "Precipitation_Maximum", "RootDepthMinimum", "SalinityTolerance", "ShadeTolerance", "TemperatureMinimum", "BloomPeriod", "CommercialAvailability", "FruitSeedPeriodBegin", "FruitSeedPeriodEnd", "Propogated_by_BareRoot", "Propogated_by_Bulbs", "Propogated_by_Container", "Propogated_by_Corms", "Propogated_by_Cuttings", "Propogated_by_Seed", "Propogated_by_Sod", "Propogated_by_Sprigs", "Propogated_by_Tubers", "Seeds_per_Pound", "SeedSpreadRate", "SeedlingVigor") VALUES (2760, NULL, 'Lactuca', 'sativa', 'Lactuca sativa', 'garden lettuce', '', '2010-10-22 13:59:26', '2011-03-01 21:02:41', 'LASA3', '', 'LASA3', 'NA (L48, CAN), PR, VI', '<strong>USA</strong> (AL, DC, DE, ID, IL, IN, MA, ME, MI, MO, ND, NJ, NM, NY, OH, OK, OR, PA, WA, WV), <strong>USA+</strong> (PR, VI), <strong>CAN</strong> (AB, ON, CA)', 'Dicot', 'Asteraceae', 'ASTERA', 'Aster family', 'Asterales', 'Asteridae', 'Magnoliopsida', '', 'Magnoliophyta', 'Spermatophyta', 'Tracheobionta', 'Plantae', 36607, 'Annual, Biennial, Perennial', 'Forb/herb', 'L48 (I), PR (I), VI (I), CAN (W)', '', '', '', '', '', '', '', '', '', '', '', '', 0, 0, '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', 0, '', '', '0.00', '0.00', 0, 0, 0, 0, 0, '', '', 0, '', '', '', '', '', '', '', '', '', '', '', '', '', 0, '', '');
select * from species where id = 2760
begin;
delete from species where scientificname not in ('Sorghum bicolor', 'Triticum durum', 'Lactuca sativa')
commit;

delete from posteriors_ensembles;
delete from ensembles;
delete from posteriors;
select count(*) from species;

select count(*) from sites where id < 200000000001 ;
delete from citations_sites where id < 200000000001 
rollback;
select * from citations_sites;

begin;
delete from sites where id < 200000000001 and sitename not like '%Season 10%' and id != 1160 and id != 6000000001;
commit;

delete from priors;

delete from pfts;
select * from methods;

begin;
delete from variables where id > 1000 and id < 6000000000;
commit;

delete from formats;
delete from mimetypes
delete from managements where id < 7000000000

delete from "attributes" where id < 7000000000

delete from models
delete from modeltypes
delete from sitegroups where id < 6000000000

delete from users where id < 6000000002 and id not in (select distinct user_id from citations) and id not in (select distinct user_id from sites where user_id is not null);

select * from users;
delete from metrics

delete from machines;

begin;
delete from citations where id < 7000000000 and id not in (select distinct citation_id from methods where citation_id is not null) and id not in (select citation_id from metrics) 
commit;

delete from metrics;
select * from methods where citation_id is not null
select * from citations;