moviedle/model/database/import_final_postgres.sql at master · Abbence/moviedle · GitHub

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
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
CREATE TEMP TABLE temp_movies (
  titleid TEXT,
  titletype TEXT,
  primarytitle TEXT,
  originaltitle TEXT,
  hungariantitle TEXT,
  year INTEGER,
  runtimeminutes INTEGER,
  genres JSONB,
  isadult BOOLEAN,
  "imdbRating" FLOAT,
  "imdbVoteCount" INTEGER,
  "directorNames" JSONB
);

COPY temp_movies (titleid, titletype, primarytitle, originaltitle, hungariantitle, year, runtimeminutes, genres, isadult, "imdbRating", "imdbVoteCount", "directorNames") from '{PATH}' WITH (FORMAT csv, DELIMITER E'\t', HEADER TRUE, NULL '\N', QUOTE E'"');

INSERT INTO movies (titleid, titletype, primarytitle, originaltitle, hungariantitle, year, runtimeminutes, genres, isadult, "imdbRating", "imdbVoteCount", "directorNames")
SELECT
  titleid,
  titletype,
  primarytitle,
  originaltitle,
  hungariantitle,
  year,
  runtimeminutes,
  CASE
    WHEN genres IS NULL THEN NULL
    ELSE ARRAY(SELECT jsonb_array_elements_text(genres))
  END,
  isadult,
  "imdbRating",
  "imdbVoteCount",
  CASE
    WHEN "directorNames" IS NULL THEN NULL
    ELSE ARRAY(SELECT jsonb_array_elements_text("directorNames"))
  END
FROM temp_movies;

DROP TABLE temp_movies;

/*
-- Exporting as JSON arrays to properly escape quotes inside arrays. Requires special loading logic (temp table) to convert back to Postgres ARRAYs.
COPY (
  SELECT
    titleid,
    titletype,
    primarytitle,
    originaltitle,
    hungariantitle,
    year,
    runtimeminutes,
    array_to_json(genres) AS genres,
    isadult,
    "imdbRating",
    "imdbVoteCount",
    array_to_json("directorNames") AS directorNames
  FROM movies
)
TO '/tmp/movies.tsv'
WITH (
  FORMAT csv,
  DELIMITER E'\t',
  HEADER true,
  NULL '\N',
  QUOTE '"',
  ESCAPE '"'
);
*/