| 1 |
5491
|
aaronmk
|
SELECT *
|
| 2 |
5721
|
aaronmk
|
FROM "nodes.src"
|
| 3 |
|
|
JOIN "names.src" USING (tax_id)
|
| 4 |
5491
|
aaronmk
|
WHERE "name class" = 'scientific name'
|
| 5 |
|
|
;
|
| 6 |
|
|
|
| 7 |
|
|
ALTER TABLE :table ALTER COLUMN tax_id TYPE integer
|
| 8 |
|
|
USING tax_id::integer;
|
| 9 |
|
|
ALTER TABLE :table ALTER COLUMN "parent tax_id" TYPE integer
|
| 10 |
|
|
USING "parent tax_id"::integer;
|
| 11 |
|
|
|
| 12 |
|
|
ALTER TABLE :table ADD PRIMARY KEY (tax_id);
|
| 13 |
5820
|
aaronmk
|
|
| 14 |
|
|
CREATE INDEX ON :table ("parent tax_id");
|
| 15 |
|
|
|
| 16 |
|
|
ALTER TABLE :table ADD FOREIGN KEY ("parent tax_id") REFERENCES :table (tax_id)
|
| 17 |
|
|
ON UPDATE CASCADE ON DELETE CASCADE;
|
| 18 |
5821
|
aaronmk
|
|
| 19 |
|
|
-- Make genus (mostly) globally unique by removing kingdom Animalia/Metazoa
|
| 20 |
|
|
DELETE FROM :table WHERE tax_id = 33208; -- delete cascades to descendants
|
