five.sql

CREATE TABLE user_shelves(
   id serial PRIMARY KEY,
   USER_id INTEGER NOT NULL REFERENCES users(id),
   shelf_id INTEGER NOT NULL REFERENCES shelves(id),
   UNIQUE(user_id, shelf_id)
);

INSERT INTO USER_shelves(user_id,shelf_id) SELECT shelves.user_id, shelves.id AS shelf_id FROM shelves

SELECT * FROM USER_shelves;
SELECT * FROM shelves;

ALTER TABLE shelves DROP COLUMN user_id;
select *FROM shelves where shelves.user_id


ALTER TABLE shelves ADD COLUMN user_id INTEGER REFERENCES users(id);
ALTER TABLE shelves ADD COLUMN shelves_users_id INTEGER REFERENCES user_shelves(id) UNIQUE ;


WITH cte AS (
  SELECT user_shelves.USER_id, user_shelves.shelf_id FROM shelves
  INNER JOIN user_shelves ON user_shelves.shelf_id = shelves.id
)

UPDATE  shelves
SET user_id =  cte.USER_id
FROM cte
WHERE shelves.id = cte.shelf_id


INSERT into shelves(name, user_id, description, shelves_users_id) (
  SELECT  shelves.name, user_shelves.user_id, shelves.description, user_shelves.id
  FROM user_shelves
  INNER JOIN shelves on user_shelves.shelf_id = shelves.id)

ON CONFLICT ON CONSTRAINT unique_shelves_users_id DO NOTHING ;