1
|
SELECT *
|
2
|
FROM "nodes.src"
|
3
|
JOIN "names.src" USING (tax_id)
|
4
|
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
|
|
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
|
|
19
|
-- Make name_txt (mostly) globally unique by removing other kingdoms
|
20
|
-- Note that the delete will cascade to descendants
|
21
|
DELETE FROM :table WHERE tax_id IN (
|
22
|
10239 -- superkingdom Viruses
|
23
|
, 12884 -- superkingdom Viroids
|
24
|
, 12908 -- unclassified sequences
|
25
|
, 28384 -- other sequences
|
26
|
, 2 -- superkingdom Bacteria
|
27
|
, 2157 -- superkingdom Archaea
|
28
|
, 4751 -- kingdom Fungi
|
29
|
, 33208 -- kingdom Animalia/Metazoa
|
30
|
);
|
31
|
|
32
|
-- Make name_txt completely globally unique by removing duplicates
|
33
|
DELETE FROM :table
|
34
|
WHERE name_txt IN (
|
35
|
SELECT name_txt
|
36
|
FROM :table
|
37
|
GROUP BY name_txt
|
38
|
HAVING count(*) > 1
|
39
|
)
|
40
|
;
|