split a string column into row and delete the original row - postgres

Question

I am an amateur in postgresql and I am looking to split a comma delimited column into many columns. I managed to split my column into rows and insert new rows based on the number of separated strings with this code

INSERT INTO myTable (username, town)
SELECT 
 username, 
 regexp_split_to_table(town, E',') 
FROM myTable;

Now what I want to achieve is, to remove the original column from database. Any advice would be appreciated.

This SQLFiddle shows what I done so far!

• user330315: No primary key in the table?
• moay: @a_horse_with_no_name well it is just a fiddle. But I really don't know. Is it really neccesary? Would that help? And how?
• user330315: It would help in deleting those unwanted rows later.
• moay: @a_horse_with_no_name Aha! And how exactly? sorry I'm a newbie in this field. :)

Answer 1

The obvious way would be to simply delete those rows that contain a ;. No new row can contain the delimiter as it won't be returned by regexp_split_to_table().

delete from yourtable
where town like '%;%';

Assuming there was a (generated) primary key in the table (e.g. named id) another option would be to use a data modifying CTE

with old_data as (
 select id, username, town
 from yourtable
), new_data as (
 -- this will generate new primary key values not present in "old_data"
 insert into yourtable (username, town)
 select username, regexp_split_to_table(town, ';')
)
delete from yourtable
where id in (select id from old_data);

---

^{Your SQL Fiddle and your question are inconsistent. The fiddle uses , but your question uses ; as a delimiter. I used ; to be consistent with your question}

• moay: Thank you for the time. I'm really grateful for the help. I wasn't familiar with the "with" syntax and should learn it but it helped me to solve the problem. Thank you.

Answer 2

You could simply flag the "old records" then delete them and remove all the flags like so:

ALTER TABLE yourTable ADD COLUMN new BOOLEAN DEFAULT false;
INSERT INTO yourTable (username, town, new)
SELECT 
 username, 
 regexp_split_to_table(town, E','),
 true
FROM yourTable;
DELETE FROM yourTable WHERE new = false;
ALTER TABLE yourTable DROP COLUMN new;

• moay: Thank you for the time. It really helped!
• grepsedawk: It's a little easier to comprehend than the solution utilizing with