X3bhqqGK

1DROP SCHEMA IF EXISTS library;
2
3CREATE SCHEMA IF NOT EXISTS library;
4
5CREATE TABLE IF NOT EXISTS library.authors (
6  uid UUID DEFAULT public.uuid_generate_v4() NOT NULL,
7  name TEXT DEFAULT NULL,
8  PRIMARY KEY(uid)
9);
10
11CREATE TABLE IF NOT EXISTS library.books (
12  uid UUID DEFAULT public.uuid_generate_v4() NOT NULL,
13  title TEXT NOT NULL,
14  isbn TEXT DEFAULT NULL,
15  PRIMARY KEY(uid),
16  UNIQUE (isbn) 
17);
18
19CREATE TABLE IF NOT EXISTS library.author_book (
20  uid UUID DEFAULT public.uuid_generate_v4() NOT NULL,
21  author UUID NOT NULL REFERENCES library.authors(uid),
22  book UUID NOT NULL REFERENCES library.books(uid),
23  PRIMARY KEY(uid),
24  UNIQUE (author, book)
25);
26
27INSERT INTO library.authors(name) VALUES('Марк Твен'), ('Илья Ильф'), ('Евгений Петров');
28
29INSERT INTO library.books(title, isbn) VALUES ('Двенадцать стульев', '978-5-389-10695-6'),
30  ('Золотой теленок', '5-7516-0314-1'),
31  ('Приключения Гекльберри Финна', '978-5-4335-0099-0'),
32  ('Сборник', NULL),
33  ('Сборник. Продолжение.', NULL);
34
35INSERT INTO library.author_book(author, book) SELECT a.uid AS author, b.uid AS book
36  FROM library.authors AS a
37    LEFT JOIN library.books AS b
38  ON (
39    (
40      b.title IN ('Двенадцать стульев', 'Золотой теленок') 
41      AND a.name IN ('Илья Ильф', 'Евгений Петров')
42    )
43    OR (
44      b.title IN ('Приключения Гекльберри Финна') 
45      AND a.name IN ('Марк Твен')
46    )
47    OR (
48      b.title IN ('Сборник', 'Сборник. Продолжение.')
49    )
50  )
51;