I currently am storing values in a column of type
array like follows (this is just an example):
CREATE TABLE blog_posts( id INTEGER PRIMARY KEY, blog_title TEXT, tags TEXT );
With the column
tags being an array of text values. I have since realized that due to the way I will be querying this data, I want to switch from using an array column to store my labels to a many-to-many lookup table instead.
CREATE TABLE blog_posts( id INTEGER PRIMARY KEY, blog_title TEXT, ); CREATE TABLE tags( id INTEGER PRIMARY KEY, label TEXT ); CREATE TABLE blog_tags( blog_id INTEGER REFERENCES blog_posts(id), tag_id INTEGER REFERENCES tags(id) );
I know how to do the alter schema commands needed to perform the migration for the schema itself but my question is how can I write a migration that will correctly transfer the data from the array to the new tables as well? (Also, I would preferably want the equivalent down migration)
Should I just write the SQL for the SELECTs and INSERTs that would be needed to accomplish this and have those run as part of the migration? Or is having any sort of SELECT or INSERT in a migration script considered a bad practice?
Should I even be trying to do a scripted migration like this at all and should this just be a manual step I have to do?
Is there some other method I have completely missed here?
Doing the needed SELECTs and INSERTs as part of the migration seems like the right approach, but I have never done a migration that required that so I find myself uncertain if that is the right approach.
This is all on PostgreSQL 9.6