blob: bfb502a96a2c3a156158554361b74a6cece03dda (
plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
|
-- link the problems to the users, creating the users as required. Removes the
-- email field from the problems.
--
-- Uses the most recently used non-anonymmous name as the name for the user.
begin;
-- create users from the problems
INSERT INTO users (email)
SELECT distinct( lower( email ) ) FROM problem;
-- add a user id to the problems
ALTER TABLE problem
ADD COLUMN user_id INT REFERENCES users(id);
-- populate the user_ids
update problem
set user_id = (
select id
from users
where users.email = lower( problem.email )
);
-- create the index now that the entries have been made
create index problem_user_id_idx on problem ( user_id );
-- add names from the problems
UPDATE users
SET name = (
select name from problem
where user_id = users.id
order by created desc
limit 1
),
phone = (
select phone from problem
where user_id = users.id
order by created desc
limit 1
);
-- make the problems user id not null etc
ALTER TABLE problem
ALTER COLUMN user_id SET NOT NULL;
-- drop emails from the problems
ALTER TABLE problem
DROP COLUMN email;
ALTER TABLE problem
DROP COLUMN phone;
commit;
|
