Split table with duplicates into 2 normalized tables?

Question

I have a table with some duplicate rows that I want to normalize into 2 tables.

user | url | keyword
-----|-----|--------
fred | foo | kw1
fred | bar | kw1
sam  | blah| kw2

I'd like to start by normalizing this into two tables (user, and url_keyword). Is there a query I can run to normalize this, or do I need to loop through the table with a script to build the tables?

Answer 1

You can do it with a few queries, but I'm not familiar with postgreSQL. Create a table users first, with an identity column. Also add a column userID to the existing table:

Then something along these lines:

INSERT INTO users (userName)
    SELECT DISTINCT user FROM url_keyword

UPDATE url_keyword
    SET userID=(SELECT ID FROM users WHERE userName=user)

Then you can drop the old user column, create the foreign key constraint, etc.