Postgres 12

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();


version


PostgreSQL 12.9 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-4), 64-bit


create table cities (
    name varchar(100),
    Latitude float,
    Longitude float
);
select * from cities;
with loc( loc_str) as ( values('London;51.50;0.12'), ('Paris;48.85;2.35')) 
insert into cities(name, Latitude,  Longitude) 
     select loc_part[1], loc_part[2]::float,loc_part[3]::float
       from ( select regexp_split_to_array(loc_str,';') loc_part from loc) l;
2 rows affected

  select * from cities;


name
latitude
longitude


London
51.5
0.12

Paris
48.85
2.35


delete from cities;
2 rows affected

create table city_stage( city_loc text);
insert into city_stage(city_loc) values('London;51.50;0.12'), ('Paris;48.85;2.35');
2 rows affected

insert into cities(name, Latitude,  Longitude) 
     select city_part[1], city_part[2]::float,city_part[3]::float
       from ( select regexp_split_to_array(city_loc,';') city_part from city_stage) l;
2 rows affected

  select * from cities;


name
latitude
longitude


London
51.5
0.12

Paris
48.85
2.35