Join Dan Sullivan for an in-depth discussion in this video Installing PostgreSQL, part of Advanced SQL for Data Scientists.
- [Instructor] It's time to install our database. We'll be using PostgreSQL, also called Postgres for our work. Let's start at the Postgres download page at www.postgresql.og/download. There are a number of options for different operating systems. I'm working on a Mac, so I'll select the link for that option. Now there are several ways to install Postgres on a Mac and I'm going to use the Enterprise DB interactive installer. It's easy to use and it's available for Mac, Windows and Linux operating systems.
So I'll click on the link for that. I'll select the most recent version of Postgres and the Mac OS X operating system. If you're using Windows, you can select the appropriate Windows version for your platform. And I'll select download now. Now that the Postgres package is downloaded, I'll open it and start the installation. Now a dialogue message will appear on a Mac operating system, but you may not see this in Windows. I'm simply going to agree to open, and I'll provide my operating system password.
The installation may be slightly different in Windows, but the process is basically the same. So, the wizard has started. I'll select next. I'm going to choose all the defaults and I'll give a password for the database. We'll want to remember that. We'll need that when we access the database. Now while Postgres is installing, I want to mention that one of the advantages of using the Enterprise DB installer, is that it automatically installs pgAdmin, a GUI admin tool that we'll be using. We don't need any additional packages, so I'll uncheck the stack builder option, and select finish.
Now, I'll start pgAdmin. Now you may see a notice that there's a newer version of pgAdmin available. We're just going to ignore that. This version works just fine for what we're doing. In the left pane, you'll see a hierarchical navigation control that includes a Postgres database. Let's open that and I'll specify the password I provided during the installation. Now, there is a Postgres database that is the default database. But I want to create a different one for our project, so I'll going to control click on the Mac, or right click in Windows, and I'm going to select create database.
I'm going to create a database called data_sci, short for data science. I'll save that. Now, let's open data_sci and navigate down through schemas, through the public schema, and let's take a look at the things that are available. We have, for example, a list of tables. If we click on that, we'll notice that there are no tables listed. We'll create tables and insert data next.
The course begins with a brief overview of SQL. Then the five major topics a data scientist should understand when working with relational databases: basic statistics in SQL, data preparation in SQL, advanced filtering and data aggregation, window functions, and preparing data for use with analytics tools.
- Data manipulation
- ANSI standards
- SQL and variations
- Statistical functions in SQL
- String, numeric, and regular expression functions in SQL
- Advanced filtering techniques
- Advanced aggregation techniques
- Windowing functions for working with ordered data sets