8h6jT01Q

1begin;
2
3drop table if exists name;
4drop table if exists node;
5
6create table node (
7    tax_id integer primary key,
8    parent_tax_id integer,
9    rank text
10);
11
12create table name (
13    tax_id integer references node (tax_id) not null,
14    name text not null,
15    unique_name text,
16    class text not null
17);
18
19\copy node (tax_id, parent_tax_id, rank) from program 'perl -pe ''s/\t\|\t/\x1f/g; s/\t\|$//'' nodes.dmp | cut -d -f1-3' with (format csv, header false, delimiter E'\x1f', quote E'\x1b', encoding 'utf-8')
20
21\copy name (tax_id, name, unique_name, class) from program 'perl -pe ''s/\t\|\t/\x1f/g; s/\t\|$//'' names.dmp' with (format csv, header false, delimiter E'\x1f', quote E'\x1b', encoding 'utf-8')
22
23create index node_rank_idx on node (rank);
24create index node_parent_idx on node (parent_tax_id);
25create index name_idx on name (name);
26-- Not actually unique, it turns out...?
27--create unique index unique_name_idx on name (unique_name);
28create index name_class_idx on name (class);
29
30update node set rank = null where rank = 'no rank';
31
32create view scientific_name as
33    select * from name where class = 'scientific name';
34
35commit;