split column into rows

global_province_synonym_queries

-- split the name_alt column into new rows using `|` as a delimiter and insert into global_province_synonyms table as new rows
-- to do: do a `IS NOT null` check?
-- note: you have to escape the | character as it is reserved in Postgres

INSERT INTO global_province_synonyms (name, rank, woe_id, country_iso3) 
SELECT 
    regexp_split_to_table(global_province_polygons.name_alt, E'\\|' ) AS name,
    1, woe_id, iso3
FROM
    global_province_polygons