mdoering

SELECT n1.rank, n1.scientific_name, u1.status,
 	f1n.scientific_name as family, o1n.scientific_name as order, c1n.scientific_name as class, p1n.scientific_name as phylum, k1n.scientific_name as kingdom
  FROM name_usage u1 join name n1 on n1.id=u1.name_fk join name n2 on n1.scientific_name=n2.scientific_name and n1.rank=n2.rank join name_usage u2 on u2.name_fk=n2.id 
  	LEFT JOIN name_usage f1 ON u1.family_fk=f1.id  LEFT JOIN name f1n ON f1n.id=f1.name_fk
  	LEFT JOIN name_usage o1 ON u1.order_fk =o1.id  LEFT JOIN name o1n ON o1n.id=o1.name_fk
  	LEFT JOIN name_usage c1 ON u1.class_fk =c1.id  LEFT JOIN name c1n ON c1n.id=c1.name_fk
  	LEFT JOIN name_usage p1 ON u1.phylum_fk=p1.id  LEFT JOIN name p1n ON p1n.id=p1.name_fk
  	LEFT JOIN name_usage k1 ON u1.kingdom_fk=k1.id LEFT JOIN name k1n ON k1n.id=k1.name_fk
  WHERE u1.dataset_key=colKey() and u2.dataset_key=colKey() and n1.rank <='GENUS'::rank and not u1.is_synonym and not u2.is_synonym and u1.id!=u2.id and u1.kingdom_fk=u2.kingdom_fk 
  ORDER by n1.rank, n1

mdoering

269a270
> Aaroniella badonneli	Aaroniella andrei
1218d1218
< ? abacta	Toechorychus abactus
1226a1227
> ? abactus	Toechorychus abactus
1433,1435c1434,1436
< Abanchogastra debilis	Spolas hawaiiensis
< Abanchogastra ferruginea	Spolas hawaiiensis
< Abanchogastra hawaiiensis	Spolas hawaiiensis

mdoering

   homonym            |  rank  |    id    |       status        |  source   | size |       accepted       |        family        |      order      |      class      |     phylum      |  kingdom  |    canonical_name    | accepted_key | family_key | order
----------------------+--------+----------+---------------------+-----------+-----------------+----------------------+----------------------+-----------------+-----------------+-----------------+-----------+----------------------+--------------+------------+------
 Admetidae            | FAMILY | 10672254 | PROPARTE_SYNONYM    | 106411888 |    0 | Cancellariidae       | Cancellariidae       | Neogastropoda   | Gastropoda      | Mollusca        | Animalia  | Admetidae            |      2303085 |    2303085 |      
 Admetidae            | FAMILY |  9639753 | PROPARTE_SYNONYM    | 106411888 |    0 | Phrynidae            | Phrynidae            | Amblypygi       | Arachnida       | Arthropoda      | Animalia  | Admetidae            |         7399 |       7399 |    

mdoering

mdoering

Plantae [kingdom]
  *Viridae [kingdom]
  Asteraceae [family]
    *Compositae [family]
    Cichorioideae [subfamily]
      Cichorieae [tribe]
        Crepis L. [genus]
          Crepis bakeri Greene [species]
            Crepis bakeri subsp. bakeri [subspecies]
            Crepis bakeri subsp. cusickii (Eastw.) Babc. & Stebbins [subspecies]

mdoering

// 20181022130930
// https://raw.githubusercontent.com/CatalogueOfLife/datapackage-specs/master/datapackage.json

{
  "name": "coldp-specs-example",
  "title": "CoL Data Package example",
  "abstract": "Data Package example taken from the CoL Data Package specification",
  "contributors": Array[1][
    {
      "name": "Markus Döring",

mdoering

-- surrogate & text keys
create table ni (
  key serial primary key,
  id text UNIQUE,
  dataset_key int,
  name text
);

create table ti (
  key serial primary key,

mdoering

DROP SCHEMA PUBLIC CASCADE;
CREATE SCHEMA PUBLIC;

CREATE TABLE dataset (
  key serial PRIMARY KEY,
  title TEXT NOT NULL,
  created TIMESTAMP WITHOUT TIME ZONE DEFAULT NOW()
);

CREATE TABLE verbatim (

mdoering

My super title

paragraph paragraph where did you go?

go straight to: number1 number2 number3 number3.1

Item number 1

mdoering

    static class Name {
        public int key;
        public int nameKey;
        public String rank;
        public String nameType;
        public String canonicalName;
        public String authorship;
        public String scientificName;
    }