From: ogjunk-pgjedan@yahoo.com
To: pgsql-sql@postgresql.org
Subject: Help: Function for splitting VARCHAR column and migrating its data to 2 new tables
Date: Mon, 23 May 2005 21:42:37 -0700 (PDT)
Message-ID: <20050524044237.64693.qmail@web31114.mail.mud.yahoo.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: ogjunk-pgjedan@yahoo.com
Subject: Re: Help: Function for splitting VARCHAR column and migrating its data to 2 new tables
In-Reply-To: <20050524044237.64693.qmail@web31114.mail.mud.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox