Import ITIS data - PQDB/metabio GitHub Wiki

CREATE TABLE itis_names AS SELECT a.tsn, complete_name as latin_name, rank_name, parent_tsn, c.vernacular_name as english_name, d.vernacular_name as french_name FROM taxonomic_units a LEFT JOIN taxon_unit_types b ON a.rank_id=b.rank_id LEFT JOIN (SELECT * FROM vernaculars WHERE language='English' OR language='Unspecified') c ON a.tsn=c.tsn LEFT JOIN (SELECT * FROM vernaculars WHERE language='French') d ON a.tsn=d.tsn WHERE a.rank_id=b.rank_id and a.kingdom_id=b.kingdom_id

DELETE FROM itis_names USING itis_names inames WHERE itis_names.tsn=inames.tsn AND itis_names.english_name

DELETE FROM itis_names USING itis_names inames WHERE itis_names.tsn=inames.tsn AND itis_names.french_name

DELETE FROM itis_names USING (SELECT tsn, string_agg(english_name,',') as english_name FROM itis_names GROUP BY tsn) inames WHERE itis_names.tsn=inames.tsn AND itis_names.english_name