4

In PostgreSQL I need to refactor a table (Purchases); it has a foreign key to another table (Shop). Instead I want two fields that keep the relation in a textual way. I must NOT lose any information, the tables already contain data.

Purchases.shop_id: (long)          -- is the field I need to drop
Purchases.shop: (characters)       -- will hold the Shop's name
Purchases.shop_user: (characters)  -- will hold the Shop's user name.

Shop.id: (long, pk)      -- still referenced from Purchases
Shop.name: (characters)  -- Shop's name
Shop.user: (characters)  -- Shop's user name

Two fields are necessary because a Shop is unique on (name,user) (or by id of course).

ALTER TABLE Purchases ADD COLUMN shop CHARACTER VARYING(255);
ALTER TABLE Purchases ADD COLUMN shop_user CHARACTER VARYING(255);

-- ???

ALTER TABLE Purchases DROP CONSTRAINT shop_id_fk;
ALTER TABLE Purchases DROP COLUMN shop_id;

So the start and the ending is easy, can somebody help with middle-part? :)

I know that foreign keys were made for this but I have to do it this way.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Adam Horvath
  • 1,249
  • 1
  • 10
  • 25

2 Answers2

3

Your seem to go the wrong way. Your original, normalized schema is typically superior. If you need to display shop / user, create a VIEW.

But you may have your reasons, so here goes:

UPDATE purchases p
SET   (shop, shop_user) = (s.name, s."user")
FROM   shop s
WHERE  s.id = p.shop_id;

Don't use the reserved word "user" as identifier.
And "name" is hardly ever a good name, either.
And varchar(255) in Postgres typically indicates a misunderstanding.

About varchar(255):

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • Thank you for the answer! The reason I need this is that Shops (as a Table) will be removed, and this is the first step in the process. – Adam Horvath Jul 03 '14 at 18:33
  • By the way; I simplified the column names and I did not run these new names against the database. Can you tell me why it's wrong to use varchar(255) in Postgres? I'm asking because we use it all over our database. – Adam Horvath Jul 03 '14 at 18:41
  • `varchar(255)`is not *wrong*. In Postgres a limit of 255 is completely arbitrary. I added some links. – Erwin Brandstetter Jul 03 '14 at 18:48
0

So it seems to me the piece you are missing is simply updating your purchases table to contain the information from your shop table. If that is correct then you could just update the table using the existing foreign key before you drop it: 

UPDATE purchases SET (shop, shop_user) =
    (SELECT name, user FROM shop
     WHERE shop.id = purchases.shop_id);
rhealitycheck
  • 650
  • 3
  • 8
  • 19