I am restructuring my DB schema and need help migrating data from 1
column of an existing table to two new tables. I have some Java code
that can do this for me, but it's very slow, and I am now hoping I can
migrate this data with some clever SQL instead.
Here are my 3 tables:
user_data (existing, old table)
---------
id (PK),
user_id (FK)
keywords VARCHAR(256)
-- this contains comma separated keywords
-- e.g. "new york,san francisco, dallas, food"
-- also "keywords without strings are really just 1 keyword"
add_date TIMESTAMP
So now I'm trying to migrate this "keywords" VARCHAR column to a more
normalized schema:
user_data_keyword (new lookup table to populate)
-----------------
id (PK) -- I may change PK to PK(user_data_id, keyword_id)
user_data_id (FK)
keyword_id (FK)
keyword (new table to populate)
-------
id (PK)
name VARCHAR(64) NOT NULL UNIQUE
add_date TIMEZONE
I just found
http://www.postgresql.org/docs/current/static/functions-string.html ,
but if anyone could lend me a hand by getting me started with writing a
function for this, I'd really appreciate it.
Thanks,
Otis
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
