blob: 6861e534025fad1a8b0664b2f8e8ae722cc6c343 (
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
|
begin;
-- create any users that don't already exist
INSERT INTO users (email)
SELECT distinct( lower( alert.email ) ) FROM alert
LEFT JOIN users ON lower( users.email ) = lower( alert.email )
WHERE users.id IS NULL;
ALTER table alert
ADD COLUMN user_id INT REFERENCES users(id);
-- populate the user ids in the alert table
UPDATE alert
SET user_id = (
SELECT id
FROM users
WHERE users.email = lower( alert.email )
);
CREATE INDEX alert_user_id_idx on alert ( user_id );
-- tidy up now everythings in place
ALTER table alert
ALTER COLUMN user_id SET NOT NULL;
ALTER table alert
DROP COLUMN email;
commit;
|
