SELECT *
FROM "nodes.src"
JOIN "names.src" USING ("taxonID")
WHERE "*name class" = 'scientific name'
;

ALTER TABLE :table ALTER COLUMN "taxonID" TYPE integer
USING "taxonID"::integer;
ALTER TABLE :table ALTER COLUMN "parentTaxonID" TYPE integer
USING "parentTaxonID"::integer;

ALTER TABLE :table ADD PRIMARY KEY ("taxonID");

CREATE INDEX ON :table ("parentTaxonID");

ALTER TABLE :table ADD FOREIGN KEY ("parentTaxonID") REFERENCES :table ("taxonID")
    ON UPDATE CASCADE ON DELETE CASCADE;

-- Make "taxonNameOrEpithet" (mostly) globally unique by removing other kingdoms
-- Note that the delete will cascade to descendants
DELETE FROM :table WHERE "taxonID" IN (
      10239 -- superkingdom Viruses
    , 12884 -- superkingdom Viroids
    , 12908 -- unclassified sequences
    , 28384 -- other sequences
    ,     2 -- superkingdom Bacteria
    ,  2157 -- superkingdom Archaea
    ,  4751 -- kingdom Fungi
    , 33208 -- kingdom Animalia/Metazoa
);

-- Make "taxonNameOrEpithet" completely globally unique by removing duplicates
DELETE FROM :table
WHERE "taxonNameOrEpithet" IN (
    SELECT "taxonNameOrEpithet"
    FROM :table
    GROUP BY "taxonNameOrEpithet"
    HAVING count(*) > 1
)
;