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!
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
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;
with