17 lines
612 B
SQL
17 lines
612 B
SQL
CREATE TABLE IF NOT EXISTS concert_diary_photos (
|
|
id SERIAL PRIMARY KEY,
|
|
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
concert_id INTEGER NOT NULL REFERENCES concerts(id) ON DELETE CASCADE,
|
|
path TEXT NOT NULL UNIQUE,
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_concert_diary_photos_entry
|
|
ON concert_diary_photos(user_id, concert_id, created_at);
|
|
|
|
INSERT INTO concert_diary_photos (user_id, concert_id, path)
|
|
SELECT user_id, concert_id, photo_path
|
|
FROM concert_diary
|
|
WHERE photo_path IS NOT NULL
|
|
ON CONFLICT (path) DO NOTHING;
|