SELECT * FROM "nodes.src" JOIN "names.src" USING (tax_id) WHERE "name class" = 'scientific name' ; ALTER TABLE :table ALTER COLUMN tax_id TYPE integer USING tax_id::integer; ALTER TABLE :table ALTER COLUMN "parent tax_id" TYPE integer USING "parent tax_id"::integer; ALTER TABLE :table ADD PRIMARY KEY (tax_id); CREATE INDEX ON :table ("parent tax_id"); ALTER TABLE :table ADD FOREIGN KEY ("parent tax_id") REFERENCES :table (tax_id) ON UPDATE CASCADE ON DELETE CASCADE; -- Make genus (mostly) globally unique by removing kingdom Animalia/Metazoa DELETE FROM :table WHERE tax_id = 33208; -- delete cascades to descendants -- Make genus completely globally unique by removing duplicates DELETE FROM :table WHERE rank IN ('genus', 'subgenus') AND name_txt IN ( SELECT name_txt FROM :table WHERE rank IN ('genus', 'subgenus') GROUP BY name_txt HAVING count(*) > 1 ) ;