taxonomicname text,

    CONSTRAINT taxonlabel_required_key CHECK ((((((((sourceaccessioncode IS NOT NULL) OR (taxonepithet IS NOT NULL)) OR (identifyingtaxonomicname IS NOT NULL)) OR (taxonomicname IS NOT NULL)) OR (taxonomicnamewithauthor IS NOT NULL)) OR (morphospecies IS NOT NULL)) OR ((parent_id IS NOT NULL) AND ((creationdate IS NOT NULL) OR (author IS NOT NULL)))))

-- Name: COLUMN taxonlabel.taxonomicname; Type: COMMENT; Schema: public; Owner: -

COMMENT ON COLUMN taxonlabel.taxonomicname IS 'The concatenated taxonomic name which uniquely identifies this taxon. Does not include the author of the name.';

    SELECT datasource.organizationname AS "dataSourceName", accepted_taxonlabel.family, accepted_taxonlabel.genus, accepted_taxonlabel.species, COALESCE(accepted_taxonlabel.taxonomicnamewithauthor, accepted_taxonlabel.taxonomicname) AS taxon, accepted_taxonlabel.author AS "taxonAuthor", accepted_taxonlabel.taxonepithet AS "taxonMorphospecies", placepath.country, placepath.stateprovince AS "stateProvince", placepath.county AS "countyParish", taxonoccurrence.verbatimcollectorname AS collector, plantobservation.collectionnumber AS "collectionNumber", array_to_string(ARRAY[identifiedby.givenname, identifiedby.middlename, identifiedby.surname], ' '::text) AS "identifiedBy", aggregateoccurrence.collectiondate AS "observationDate", location.authorlocationcode AS "plotCode", functions._m2_to_ha(location.area_m2) AS "plotAreaHa", method.name AS "plotMethod", locationcoords.latitude_deg AS latitude, locationcoords.longitude_deg AS longitude, location.elevation_m, taxonoccurrence.iscultivated AS "isCultivated", taxonoccurrence.cultivatedbasis AS "isCultivatedReason", _fraction_to_percent(aggregateoccurrence.cover_fraction) AS "pctCover" FROM ((((((((((((((location JOIN party datasource ON (((datasource.party_id = location.creator_id) AND (datasource.organizationname IS NOT NULL)))) LEFT JOIN locationcoords USING (location_id)) LEFT JOIN locationplace USING (location_id)) LEFT JOIN placepath USING (placepath_id)) JOIN locationevent USING (location_id)) LEFT JOIN method USING (method_id)) JOIN taxonoccurrence USING (locationevent_id)) JOIN taxondetermination USING (taxonoccurrence_id)) LEFT JOIN party identifiedby ON ((identifiedby.party_id = taxondetermination.party_id))) JOIN taxonlabel datasource_taxonlabel USING (taxonlabel_id)) JOIN taxonlabel accepted_taxonlabel ON ((accepted_taxonlabel.taxonlabel_id = datasource_taxonlabel.canon_label_id))) LEFT JOIN aggregateoccurrence USING (taxonoccurrence_id)) LEFT JOIN plantobservation USING (aggregateoccurrence_id)) LEFT JOIN specimenreplicate USING (plantobservation_id));

CREATE UNIQUE INDEX taxonlabel_unique ON taxonlabel USING btree ((COALESCE(parent_id, 2147483647)), (COALESCE(taxonepithet, '\\N'::text)), (COALESCE(rank, 'unknown'::taxonrank)), creator_id, (COALESCE(sourceaccessioncode, '\\N'::text)), (COALESCE(creationdate, 'infinity'::date)), (COALESCE(identifyingtaxonomicname, '\\N'::text)), (COALESCE(taxonomicname, '\\N'::text)), (COALESCE(author, '\\N'::text)), (COALESCE(taxonomicnamewithauthor, '\\N'::text)), (COALESCE(morphospecies, '\\N'::text)));