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

)

;