PostgreSQL Database Set-Up on MacOS: PGAdmin and Postegreapp
An A to Z database set-up and file import guide for both applications and then comparing the two (Hint: one’s a clear winner)
For my latest portfolio project, I have been using 2019 New York City Airbnb data and Yellow Taxi ride data to paint a picture of tourism in Manhattan. My goal was to build a local database I could practice my ETL (Exchange, Transform, and Loading) skills in SQL.
As I scoured over the internet, I could tell there was a basic need for this kind of written tutorial for PostgreSQL (pgsql for short), so in this article I share my process and what I learned from experimenting with both pgAdmin and Postgreapp. For the purposes of this tutorial, I set up two separate servers, one on each application, and import into each just one csv, location zone keys for taxi rides, called Taxi Zone Lookup Table (CSV) towards the bottom of the webpage.
To help follow along, I also recommend these two video tutorials for setting up databases on Postgresapp and PGAdmin:
Initial Note: Downloading PostgreSQL
For both applications, we will need pgsql up and running. The easiest way of installation is to run this single command line prompt in Terminal:
brew install postgresql
More instructions for homebrew-based installation can be found here.
You can also download the package from the website with an interactive installer. You can download the installer here from PostgreSQL’s website. It’s important to note that you can run PostgreSQL databases solely from your terminal. This is the hardest way of system management because you don’t get dashboard management tools like with pgAdmin and Postgreapp.
Postgresapp
Installing Postgresapp & Initializing a Database
As an application, you will be downloading it with an installer, available here. Let the installer run, and drag the application to your applications folder when prompted. When you open the application, a default postgreSQL server will be active, named after the version of pgsql you have installed, with 3 different databases already inside of it. Initial servers are default set up in port 5432, unless that port is already full. When you create a new database, you will have to use a different port — note how I have set my other server to port 5440.
There should be a green checkmark indicating the server is running, as seen above. For more clarification, you can follow along the installation process in this video here by Recoding.
Now that you have a server up and running, you can click on any of the three database icons to open the terminal for psql coding. To create a database you will simply type (name it however you like):
CREATE DATABASE database_name;
Now you have a running database!
Creating a Table and Importing Data
Following the above instructions, you will now have a visible database in your postgreapp. You can click on the icon to open its terminal and begin using the psql commands to build up a table. As stated earlier, I am using a NYC-government sourced csv file of taxi area zones for this demonstration. We must first create a table that matches the csv data for easy importation. In the terminal, by clicking on the database we made above, we can input the following code:
CREATE TABLE location_keys (locationid INTEGER, borough VARCHAR(13), zone VARCHAR(50), service_zone VARCHAR(13));
I inspected the csv before determining which type of variable each one and how much space each string needed. If I underestimate the number of characters necessary, I run the risk of an error in the event a string is too big. More information on character datatypes and storage optimization can be found here.
Now the table is created, it’s just a matter of importing from the source location. Download the taxi_zone_lookup.csv and find it in your downloads, right click it and press option to copy it as a Pathname (/Users/username/Downloads/taxi+_zone_lookup.csv). The filepath is key for the sql command to import. Now, inside the terminal psql for your database, you will run the following code:
COPY location_keys(locationid, borough, zone, service_zone)
FROM ‘/Users/jadeadams/Downloads/data/taxi_zone_lookup.csv’
DELIMITER ‘,’
CSV HEADER;
In the copy query, make sure you use forward slashes as above in the pathname and not backslashes. Also remove the ‘C:’ if any. Now, if you run a select query on your table inside the database’s psql, the table will be filled with the csv data, as below. You now have a fully functioning table.
PGADmin
Installing PGAdmin
You can download the installer for MacOS here. Click it to run and follow the prompts, dragging the application into the Applications folder when requested. Reference the videos I listed above for any extra clarification on installation. Just like with Postgreapp, PGAdmin starts with a server running and some databases. inside of it. To create a new database, you can right click in the left toolbar as below and click on Create => Database. (Note that if you right click on servers you can also create a new server, keep in mind you can’t use the same ports as you did in Postgreapp)
Creating a Table and Importing Data
Now with a named database, we can create a table using multiple methods. Inside each database, PGAdmin has a very user-friendly dashboard. One can even make tables without knowing SQL. However, for our intents and purposes, we are going to run queries. Clicking the furthest left black icon, depicted below, allows us to open a dashboard built for runing queries. Make sure you are clicked into the correct database by checking the pathname of the dashboard in the top right.
Within the query dashboard, we run the same exact create table command as we did in Postgreapp, for the location zones.
CREATE TABLE location_keys (locationid INTEGER, borough VARCHAR(13), zone VARCHAR(50), service_zone VARCHAR(13));
Click the Run/Execute button in the top toolbar executes the query. Notice in the bottom toolbar, the result of the query is posted: the time it took (in ms or s) and whether it was successful or an error is shown.
Now, we can run the same import in the query table.
If it was written correctly, it should be returned quickly. And now you have two fully functioning databases. You can click on query history to view all the queries you’ve done if you’ve every want to look back.
Conclusion: Why PGAdmin is Better than Postgresapp
Given its strong variety of dashboard capabilities, including its query history tool, the psql terminal, and ability to examine schema like tables and relationships, PGAdmin is well suited for tracking a local database and managing a variety of servers. Inside any database’s left toolbar menu, clicking on the red icon labelled Schema allows you to see the variety of current. You can even pair foreign tables and track the relationships. Meanwhile, Postgreapp is quite Spartan: only new servers can be set up in its dashboard, anything else has to be completed in the Mac’s terminal window. PGAdmin, instead of sending users to the MacOS terminal, can opens terminals virtually through its app, allowing users to keep access to its handy dashboard information while they perform queries.
Furthermore, take a look at the databases listed in PGAdmin and you will realize that the databases and servers made in Postgreapp are also in PGAdmin; however, vice versa cannot be said: Your PGAdmin-made databases are nowhere to be found in Postgreapp. PGAdmin does an excellent job of keeping stock of your computers full psql servers. Lastly, PGAdmin has excellent documentation and forums online for solving issues. I will be eagerly using it for my project as I practice my skills in ETL and RDBMS.
Feedback is always welcome: comment here, inMail me on LinkedIn or email me at jade.adams517@gmail.com. In my next article, I will breaking down how to utilize loops in terminal and in psql to import whole folders of files into SQL databases, so stay tuned by following my Medium account and subscribing to my Medium mailing list! :)
Index
This article is indebted to several online contributors work.
- Recoding. “How to Install PostgreSQL on MacOS | Postgres App & PgAdmin — YouTube.” Www.youtube.com, www.youtube.com/watch?v=QaZrWIvAFsA. Accessed 16 May 2022.
- Meyers, Jon. “Set up a Local PostgreSQL Database on MacOS — YouTube.” Www.youtube.com, Prisma, 23 June 2021, www.youtube.com/watch?v=wTqosS71Dc4. Accessed 16 May 2022.
- “Import CSV File into PosgreSQL Table.” PostgreSQL Tutorial, www.postgresqltutorial.com/postgresql-tutorial/import-csv-file-into-posgresql-table/. Accessed 16 May 2022.
