Setup sample database in Postgres
Setup Postgres sample database
In this post, I will introduce you to a PostgreSQL sample database that you can use for learning and practice PostgreSQL. We will use the DVD rental database for demonstrating the features of PostgreSQL. The DVD rental database represents the business processes of a DVD rental store. The DVD rental database has many objects including:
- 15 tables
- 1 trigger
- 7 views
- 8 functions
- 1 domain
- 13 sequences
Tables
There are 15 tables in the DVD Rental database:
actor– stores actors data including first name and last name.film– stores films data such as title, release year, length, rating, etc.film_actor– stores the relationships between films and actors.category– stores film’s categories data.film_category- stores the relationships between films and categories.store– contains the store data including manager staff and address.inventory– stores inventory data.rental– stores rental data.payment– stores customer’s payments.staff– stores staff data.customer– stores customers data.address– stores address data for staff and customerscity– stores the city names.country– stores the country names.
ER diagram
Load the database into Postgres server
The database can be downloaded using from here TODO which is of the form database.tar. This file needs to loaded into Postgres server.
Create a new database: We need to create a new database in PostgresSQL database server before loading database schema and data into the database.
- Launch
pgAdminwindow and connect to the localhost server (if you have postgres database server running on a remote host, then specify the url/port). - Create database
dvdrental. Next, right click and clickRestore
- Right click on the
dvdrentaldatabase and restore.
NOTE: If you see any errors during restoration with regards to role: postgres not available, then create that role.
Query the database
%load_ext sql
%sql postgresql://shravan:shravan@127.0.0.1/dvdrental
'Connected: shravan@dvdrental'
%%sql
-- Query the right table in information_schema
SELECT table_name
FROM information_schema.tables
-- Specify the correct table_schema value
WHERE table_schema = 'public';
* postgresql://shravan:***@127.0.0.1/dvdrental
22 rows affected.
| table_name |
|---|
| actor |
| actor_info |
| customer_list |
| film_list |
| nicer_but_slower_film_list |
| sales_by_film_category |
| store |
| sales_by_store |
| staff_list |
| address |
| category |
| city |
| country |
| customer |
| film_actor |
| film_category |
| inventory |
| language |
| rental |
| staff |
| payment |
| film |
%%sql
SELECT
*
FROM customer
LIMIT 10;
* postgresql://shravan:***@127.0.0.1/dvdrental
10 rows affected.
| customer_id | store_id | first_name | last_name | address_id | activebool | create_date | last_update | active | |
|---|---|---|---|---|---|---|---|---|---|
| 524 | 1 | Jared | Ely | jared.ely@sakilacustomer.org | 530 | True | 2006-02-14 | 2013-05-26 14:49:45.738000 | 1 |
| 1 | 1 | Mary | Smith | mary.smith@sakilacustomer.org | 5 | True | 2006-02-14 | 2013-05-26 14:49:45.738000 | 1 |
| 2 | 1 | Patricia | Johnson | patricia.johnson@sakilacustomer.org | 6 | True | 2006-02-14 | 2013-05-26 14:49:45.738000 | 1 |
| 3 | 1 | Linda | Williams | linda.williams@sakilacustomer.org | 7 | True | 2006-02-14 | 2013-05-26 14:49:45.738000 | 1 |
| 4 | 2 | Barbara | Jones | barbara.jones@sakilacustomer.org | 8 | True | 2006-02-14 | 2013-05-26 14:49:45.738000 | 1 |
| 5 | 1 | Elizabeth | Brown | elizabeth.brown@sakilacustomer.org | 9 | True | 2006-02-14 | 2013-05-26 14:49:45.738000 | 1 |
| 6 | 2 | Jennifer | Davis | jennifer.davis@sakilacustomer.org | 10 | True | 2006-02-14 | 2013-05-26 14:49:45.738000 | 1 |
| 7 | 1 | Maria | Miller | maria.miller@sakilacustomer.org | 11 | True | 2006-02-14 | 2013-05-26 14:49:45.738000 | 1 |
| 8 | 2 | Susan | Wilson | susan.wilson@sakilacustomer.org | 12 | True | 2006-02-14 | 2013-05-26 14:49:45.738000 | 1 |
| 9 | 2 | Margaret | Moore | margaret.moore@sakilacustomer.org | 13 | True | 2006-02-14 | 2013-05-26 14:49:45.738000 | 1 |
%%sql
SELECT *
FROM film
LIMIT 10;
* postgresql://shravan:***@127.0.0.1/dvdrental
10 rows affected.
| film_id | title | description | release_year | language_id | rental_duration | rental_rate | length | replacement_cost | rating | last_update | special_features | fulltext |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 133 | Chamber Italian | A Fateful Reflection of a Moose And a Husband who must Overcome a Monkey in Nigeria | 2006 | 1 | 7 | 4.99 | 117 | 14.99 | NC-17 | 2013-05-26 14:50:58.951000 | ['Trailers'] | 'chamber':1 'fate':4 'husband':11 'italian':2 'monkey':16 'moos':8 'must':13 'nigeria':18 'overcom':14 'reflect':5 |
| 384 | Grosse Wonderful | A Epic Drama of a Cat And a Explorer who must Redeem a Moose in Australia | 2006 | 1 | 5 | 4.99 | 49 | 19.99 | R | 2013-05-26 14:50:58.951000 | ['Behind the Scenes'] | 'australia':18 'cat':8 'drama':5 'epic':4 'explor':11 'gross':1 'moos':16 'must':13 'redeem':14 'wonder':2 |
| 8 | Airport Pollock | A Epic Tale of a Moose And a Girl who must Confront a Monkey in Ancient India | 2006 | 1 | 6 | 4.99 | 54 | 15.99 | R | 2013-05-26 14:50:58.951000 | ['Trailers'] | 'airport':1 'ancient':18 'confront':14 'epic':4 'girl':11 'india':19 'monkey':16 'moos':8 'must':13 'pollock':2 'tale':5 |
| 98 | Bright Encounters | A Fateful Yarn of a Lumberjack And a Feminist who must Conquer a Student in A Jet Boat | 2006 | 1 | 4 | 4.99 | 73 | 12.99 | PG-13 | 2013-05-26 14:50:58.951000 | ['Trailers'] | 'boat':20 'bright':1 'conquer':14 'encount':2 'fate':4 'feminist':11 'jet':19 'lumberjack':8 'must':13 'student':16 'yarn':5 |
| 1 | Academy Dinosaur | A Epic Drama of a Feminist And a Mad Scientist who must Battle a Teacher in The Canadian Rockies | 2006 | 1 | 6 | 0.99 | 86 | 20.99 | PG | 2013-05-26 14:50:58.951000 | ['Deleted Scenes', 'Behind the Scenes'] | 'academi':1 'battl':15 'canadian':20 'dinosaur':2 'drama':5 'epic':4 'feminist':8 'mad':11 'must':14 'rocki':21 'scientist':12 'teacher':17 |
| 2 | Ace Goldfinger | A Astounding Epistle of a Database Administrator And a Explorer who must Find a Car in Ancient China | 2006 | 1 | 3 | 4.99 | 48 | 12.99 | G | 2013-05-26 14:50:58.951000 | ['Trailers', 'Deleted Scenes'] | 'ace':1 'administr':9 'ancient':19 'astound':4 'car':17 'china':20 'databas':8 'epistl':5 'explor':12 'find':15 'goldfing':2 'must':14 |
| 3 | Adaptation Holes | A Astounding Reflection of a Lumberjack And a Car who must Sink a Lumberjack in A Baloon Factory | 2006 | 1 | 7 | 2.99 | 50 | 18.99 | NC-17 | 2013-05-26 14:50:58.951000 | ['Trailers', 'Deleted Scenes'] | 'adapt':1 'astound':4 'baloon':19 'car':11 'factori':20 'hole':2 'lumberjack':8,16 'must':13 'reflect':5 'sink':14 |
| 4 | Affair Prejudice | A Fanciful Documentary of a Frisbee And a Lumberjack who must Chase a Monkey in A Shark Tank | 2006 | 1 | 5 | 2.99 | 117 | 26.99 | G | 2013-05-26 14:50:58.951000 | ['Commentaries', 'Behind the Scenes'] | 'affair':1 'chase':14 'documentari':5 'fanci':4 'frisbe':8 'lumberjack':11 'monkey':16 'must':13 'prejudic':2 'shark':19 'tank':20 |
| 5 | African Egg | A Fast-Paced Documentary of a Pastry Chef And a Dentist who must Pursue a Forensic Psychologist in The Gulf of Mexico | 2006 | 1 | 6 | 2.99 | 130 | 22.99 | G | 2013-05-26 14:50:58.951000 | ['Deleted Scenes'] | 'african':1 'chef':11 'dentist':14 'documentari':7 'egg':2 'fast':5 'fast-pac':4 'forens':19 'gulf':23 'mexico':25 'must':16 'pace':6 'pastri':10 'psychologist':20 'pursu':17 |
| 6 | Agent Truman | A Intrepid Panorama of a Robot And a Boy who must Escape a Sumo Wrestler in Ancient China | 2006 | 1 | 3 | 2.99 | 169 | 17.99 | PG | 2013-05-26 14:50:58.951000 | ['Deleted Scenes'] | 'agent':1 'ancient':19 'boy':11 'china':20 'escap':14 'intrepid':4 'must':13 'panorama':5 'robot':8 'sumo':16 'truman':2 'wrestler':17 |