Split comma separated value and insert into new columns on each row

I have a table where one of the columns, fullname is a comma separated value. For example, one of columns has first_name, last_name.

I need to add two new columns, firstname and lastname to the table and split the value of the fullname column into the two new columns, for each row.

I am able to split the values of the fullname column:

select split_part(fullname, ',', 1) as firstname, split_part(fullname, ',', 2) as lastname from test

I am not sure how to insert the first and last names into the corresponding rows.

I tried with a CTE:

with temp as 
  (select split_part(fullname, ',', 1) as firstname, split_part(fullname, ',', 2) as lastname from test) 
insert into 
  test(firstname) 
  select firstname 
    from temp 
    where position(temp.firstname in test.fullname) > 0

but this gives an error.

I am not sure what the right approach is. Any tips will be appreciated.

Solution

Assuming you have added new columns to the table:

alter table test add column firstname text;
alter table test add column lastname text;

To populate those columns:

update test set
  firstname = split_part(fullname, ',', 1),
  lastname = split_part(fullname, ',', 2);

Alternatively, you can create a view that calculates the columns on the fly:

create view test_full as (
  select
    *,
    split_part(fullname, ',', 1) as firstname,
    split_part(fullname, ',', 2) as lastname
  from test
);

A view as the advantage that you wouldn't have to re-populate the new columns when rows are added, but the disadvantage that you couldn't update the new columns and performance would be somewhat slower (but not a lot slower).