PostgreSQL - How do you create a "dimension" table from a 'select distinct' query and create a primary and foreign key?

Question

I have a fact table with many entries, and they have 'ship to' columns that are very closely related, but none of the columns are always unique. I would like to make a dimension table for this, and reference the new dimension table rows using a key.

I can create the new dimension table with a create table as select distinct, and I can add a row number primary key for it, but I'm not sure how to put the matching foreign key into the fact table, where they match.

I could easily create a new foreign key column, and fill it using a where to match the old distinct rows in the fact table to the distinct rows in the dimension table, but there is no easy column to match (since there is no key yet), so do I need to create a 'where' match that matches all of the columns together, then assigns the primary key from the dimension table?

I may just be lazy, and don't want to research how to create alter queries and how to create complex where matching, but it seems like a pretty common action for database management, so I feel like it might help others.

Answer 1

I guess one way to do it is to create a concatenation of all of the values in all of the columns for each row in the new dimension to make a unique identifier from the data, then do the same thing for the columns in the fact dimension. Now there is a unique key between the two, and this can be converted to an integer id. Add a new sequence column in the new dimension, then create a new column in the fact, and set it to the integer id in the new dimension where the concatenated id is the same.

This is obviously very inefficient, as the whole contents of the new dimension have to be duplicated, as well as that again in the fact dimension - just to create a link.