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 other kingdoms
-- Note that the delete will cascade to descendants
DELETE FROM :table WHERE tax_id = 33208; -- kingdom Animalia/Metazoa

-- 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
)
;
