Project

General

Profile

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
;
(3-3/9)