stats170ab-2018: postgresloading.sql

File postgresloading.sql, 2.0 KB (added by mjcarey, 3 years ago)

PostgreSQL data loading script (if you need it)

Line 
1CREATE TABLE titleBasics (
2    tconst         varchar(10) PRIMARY KEY,
3    titleType      varchar(20),
4    primaryTitle   varchar(400),
5    originalTitle  varchar(400),
6    isAdult        int,
7    startYear      int,
8    endYear        int,
9    runtimeMinutes int,
10    genres         varchar(100)
11);
12
13\COPY titleBasics FROM '/Users/mikejcarey/IMDBData/title.basics.tsv' QUOTE '|' DELIMITER E'\t' NULL '\N' CSV HEADER;
14
15CREATE TABLE titleRatings (
16    tconst         varchar(10) PRIMARY KEY,
17    averageRating  float,
18    numVotes       int
19);
20
21\COPY titleRatings FROM '/Users/mikejcarey/IMDBData/title.ratings.tsv' QUOTE '|' DELIMITER E'\t' NULL '\N' CSV HEADER;
22
23CREATE TABLE titleCrew (
24    tconst         varchar(10) PRIMARY KEY,
25    directors      varchar(5000),
26    writers        varchar(7000)
27);
28
29\COPY titleCrew FROM '/Users/mikejcarey/IMDBData/title.crew.tsv' QUOTE '|' DELIMITER E'\t' NULL '\N' CSV HEADER;
30
31CREATE TABLE titleEpisode (
32    tconst         varchar(10) PRIMARY KEY,
33    parentTconst   varchar(10),
34    seasonNumber   int,
35    episodeNumber  int
36);
37
38\COPY titleEpisode FROM '/Users/mikejcarey/IMDBData/title.episode.tsv' QUOTE '|' DELIMITER E'\t' NULL '\N' CSV HEADER;
39
40CREATE TABLE titlePrincipals (
41    tconst         varchar(10) PRIMARY KEY,
42    principalCast  varchar(200)
43);
44
45\COPY titlePrincipals FROM '/Users/mikejcarey/IMDBData/title.principals.tsv' QUOTE '|' DELIMITER E'\t' NULL '\N' CSV HEADER;
46
47CREATE TABLE titleAkas (
48    titleId varchar(10),
49    ordering int,
50    title varchar(1000),
51    region varchar(100),
52    language varchar(100),
53    types varchar(100),
54    attributes varchar(100),
55    isOriginalTitle int
56);
57
58\COPY titleAkas FROM '/Users/mikejcarey/IMDBData/title.akas.tsv' QUOTE '|' DELIMITER E'\t' NULL '\N' CSV HEADER;
59
60CREATE TABLE nameBasics (
61    nconst varchar(10),
62    primaryName varchar(400),
63    birthYear int,
64    deathYear int,
65    primaryProfession varchar(100),
66    knownForTitles varchar(100)
67);
68
69\COPY nameBasics FROM '/Users/mikejcarey/IMDBData/name.basics.tsv' QUOTE '|' DELIMITER E'\t' NULL '\N' CSV HEADER;
70