CREATE TABLE name (
id TEXT primary key,
authors_t TEXT,
basionym_s_lower TEXT,
basionym_author_s_lower TEXT,
lookup_basionym_id TEXT,
bibliographic_reference_s_lower TEXT,
bibliographic_type_info_s_lower TEXT,
reference_collation_s_lower TEXT,
collection_date_as_text_s_lower TEXT,
collection_day_1_s_lower TEXT,
collection_day_2_s_lower TEXT,
collection_month_1_s_lower TEXT,
collection_month_2_s_lower TEXT,
collection_number_s_lower TEXT,
collection_year_1_s_lower TEXT,
collection_year_2_s_lower TEXT,
collector_team_as_text_t TEXT,
lookup_conserved_against_id TEXT,
lookup_correction_of_id TEXT,
date_created_date TEXT,
date_last_modified_date TEXT,
distribution_s_lower TEXT,
east_or_west_s_lower TEXT,
family_s_lower TEXT,
taxon_scientific_name_s_lower TEXT,
taxon_sci_name_not_suppressed_s_lower TEXT,
genus_s_lower TEXT,
geographic_unit_as_text_s_lower TEXT,
hybrid_b BOOLEAN,
hybrid_genus_b BOOLEAN,
lookup_hybrid_parent_id TEXT,
hybrid_parents_s_lower TEXT,
infra_family_s_lower TEXT,
infra_genus_s_lower TEXT,
infraspecies_s_lower TEXT,
lookup_isonym_of_id TEXT,
lookup_later_homonym_of_id TEXT,
latitude_degrees_s_lower TEXT,
latitude_minutes_s_lower TEXT,
latitude_seconds_s_lower TEXT,
locality_s_lower TEXT,
longitude_degrees_s_lower TEXT,
longitude_minutes_s_lower TEXT,
longitude_seconds_s_lower TEXT,
name_status_s_lower TEXT,
name_status_bot_code_type_s_lower TEXT,
name_status_editor_type_s_lower TEXT,
nomenclatural_synonym_s_lower TEXT,
lookup_nomenclatural_synonym_id TEXT,
north_or_south_s_lower TEXT,
original_basionym_s_lower TEXT,
original_basionym_author_team_s_lower TEXT,
original_hybrid_parentage_s_lower TEXT,
original_remarks_s_lower TEXT,
original_replaced_synonym_s_lower TEXT,
original_taxon_distribution_s_lower TEXT,
lookup_orthographic_variant_of_id TEXT,
other_links_s_lower TEXT,
lookup_parent_id TEXT,
publication_s_lower TEXT,
lookup_publication_id TEXT,
publication_year_i TEXT,
publication_year_full_s_lower TEXT,
publication_year_note_s_lower TEXT,
publishing_author_s_lower TEXT,
rank_s_alphanum TEXT,
reference_t TEXT,
reference_remarks_s_lower TEXT,
remarks_s_lower TEXT,
lookup_replaced_synonym_id TEXT,
lookup_same_citation_as_id TEXT,
score_s_lower TEXT,
species_s_lower TEXT,
species_author_s_lower TEXT,
lookup_superfluous_name_of_id TEXT,
suppressed_b BOOLEAN,
top_copy_b BOOLEAN,
lookup_type_id TEXT,
type_locations_s_lower TEXT,
type_name_s_lower TEXT,
type_remarks_s_lower TEXT,
type_chosen_by_s_lower TEXT,
type_note_s_lower TEXT,
detail_author_team_ids TEXT,
detail_species_author_team_ids TEXT,
page_as_text_s_lower TEXT,
citation_type_s_lower TEXT,
lookup_validation_of_id TEXT,
version_s_lower TEXT,
powo_b BOOLEAN,
sortable TEXT
);
\copy name from 'ipniWebName.csv' with CSV DELIMITER '|' HEADER
\copy (SELECT id,taxon_scientific_name_s_lower,authors_t,rank_s_alphanum,family_s_lower,'urn:lsid:ipni.org:names:' || lookup_nomenclatural_synonym_id,'urn:lsid:ipni.org:names:' || lookup_parent_id,lookup_publication_id,publication_year_i,reference_t,citation_type_s_lower,'https://www.ipni.org/n/' || id FROM name WHERE top_copy_b AND NOT suppressed_b) to 'ipni.csv' with CSV