How did I install Postgres.app and PgAdmin and build the Chinook Database?

Photo by Markus Spiske on Unsplash

I am taking the Udacity Business Analytics Nanodegree. As of this writing I am working on my SQL project which requires querying the chinook database.

It was easy to get started using the instructions provided regarding how to query the database using DB browser for SQLite, but I did not like the lay out and would have preferred to set it up myself using Postgres.app and PgAdmin.

So after hours of looking for resources on how to set it up, here’s what worked for me.

This set up is done in Mac.

Here is the breakdown

1. Install Postgres.app — This provides you a postgresql server.

2. Install a GUI — To make it easier to work with our database instead of interacting via command line.

Postgres.app recommends two:

• pgAdmin
• Postico

3. Populate and configure the database — Download sql file and alter table names and columns.

Install Postgres.app

Go to Postgres.app downloads page and follow installation prompts.

I found this YouTube links useful:

1. How to install PostgreSQL on macOS Postgres App & PgAdmin
2. How to install PostgreSQL on Mac OS X

After installing the Postgres.app when you click Initialize it will start and create 3 databases automatically.

1. your_username
2. postgres
3. template1

Note that I already had mine set up so there is more than 3

Then after that configure your path in the terminal, this will allow you to type psqlcommands in the terminal you can find the script here at step 3.

After re starting your terminal window…

Clicking any of the databases in Postgres.app, a will open up a terminal window and you can type any psql commands.

But this isn’t really ideal to to work with so we’ll install a GUI. We are going to use pgAdmin.

Install pgAdmin

Go to download page and follow the installation prompts.

After installing, pgAdmin will open in your browser and will ask you for a master password.

I found this video useful.

After that establish a connection between the Postgres.app server to pgAdmin by creating a server and then connecting that server to Postgres.app.

• Right click server

Create Server

Under this tabs:

• General provide a name
• Connection use the default provided by Postgres.app

And voila your in business!

I have more than 3 since I already set them up.

You will have three databases created by default by Postgres.app.

Now you can just go ahead and skip to the Chinook database part or you can create an authenticated user.

Create a user

• Right click Login/Group Role then Create Login/Group Role

Under this tabs:

• General provide a name
• Definition provide a password
• Privileges toggle on all options

Now you can you can experiment around creating tables and populating it with data similar to this link How to install PostgreSQL on macOS & Postgres App & PgAdmin.

Or go ahead and create the Chinook Database!

Create Database

In pgAdmin

• Right click Database Create Database

Provide a name to your database. I have mine set up as ChinookDemo, then select who owns it.

Then Save

Click it to connect! Make sure Postgres.app is running in the background because this is our server.

Now let’s go and set up the Chinook database!

The Chinook database

The Chinook database is a sample database that is based on a music store. It holds information about the artists, songs, customers, employees, invoices and many more data.

Read more here.

Here is the ERD(Entity Relationship Diagram)

The Chinook Database

1. I navigated to the download page and looked for Chinook_PostgreSql.sql

2. Click Download or View raw

The database build will open up

3. cmd + a to copy it your clip board

4. Go to pgAdmin and right click the empty ChinookDemo database that we have just created and select Query Tool

This will open the Query editor window

Then paste or cmd + v it the Query editor

Then press f5 or the play button

After that our database is created! 👍

You can clear the query window by clicking this

Navigate to the Tables

Right click any one of them to view the seeded database

Let’s view it!

You can just go ahead and query from this but look closely at the script

SELECT * FROM public."Employee"
 ORDER BY "EmployeeId" ASC;

You will have to query it using " "and prepending it with .public
This will slow us down so much!

This happened because the original file was created with double quotes in them " ".

Let’s alter it so we can query much faster!

I had to rename all of the Tables and columns without " "

Open up this file View raw the chinookFixer.sql

Copy this to your clip board and execute in your Query editor window.

Let’s try to query it again

SELECT * FROM public.Employee
 ORDER BY EmployeeId;

To avoid having to prepend .public after our From clause we will have to configure the search_path so pgAdmin will know that our data is inside the public schema.

To configure the search_path

In your terminal run this script Set search_path = my_schema, "$user",public;

Note that this is only for current session

Or you can alter your role so you don’t have to every time you open pgAdmin

Read more here.

Try to query it again!

This time let’s answer one of the quiz question.

Which city has the best customers?

SELECT BillingCity,
 SUM(total)
 FROM Invoice
 GROUP BY 1
 ORDER BY 2 DESC

Or

SELECT billingcity,
 SUM(total)
 FROM invoice
 GROUP BY 1
 ORDER BY 2 DESC

Both will work 🚀

Hope you find this helpful! 👍