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

)

;