Clever Geek Handbook

Split table with duplicates into 2 normalized tables?

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

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

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

+2


source to share


1 answer


You can do this with multiple queries, but I'm not familiar with postgreSQL. First, create table users with an identity column. Also add the user id of the column 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 a foreign key constraint, etc.

+4


source






More articles:


All Articles