by Matthew Barlowe


This tutorial will cover the basics of getting PostgreSQL setup on your computer and running. We won’t actually be working with data in this article, but instead the focus is on the nuts and bolts of what PostgreSQL is and and its structure. But before I get into all that let’s start at the beginning. As always these tutorials will be focused on working in a Mac OS X environment. The Postgres commands and the SQL syntax should work no matter what, but the details of installing will be different on a Windows system.

What is SQL?

SQL, or sequel as its commonly pronounced, is bascially an acronym that stands for Structured Querying Language. SQL has been around forever since 1974 and isn’t anywhere near cutting edge in technology. Some may see this as a drawback, but it also has its advantages as well. One being that it’s a well documented system and language, and two is that most people are at least somewhat familiar with its terminology.

But what does SQL do? Well SQL is the language used to query and manipulate data in a relational database system. And a relational database system is a system that uses the relational model; a model consisting of unique keys to create relations between different tables on the database. Its not really important you understand this all right now but just keep it in mind for future tutorials.

What is PostgreSQL?

PostgreSQL is just a flavor of SQL. I chose this one for several reasons. One being it is noted for its speed, and secondly because it is open source and therefore free. There are plenty other choices out there such as MySQL or SQLite3 that are perfectly suitable, but these tutorials will focus on Postgres as it is the one I am most comfortable using. There are differences between these options, but the basic commands we wil go over in this series should be universal between all of these systems, however I can’t speak for all of them as there may be slight differences in syntax.

Still the majority of the lessons learned using PostgreSQL and these tutorials will be portable.

Installing PostgreSQL

To get PostgreSQL onto our system we are going to use Homebrew to install the files. If you don’t have Homebrew please refer to this tutorial to get it up and running. So like our other brew installations, Postgres is installed with a command you may find familiar if you’ve been following these tutorials.


$ brew install postgresql

And you’ll see a whole bunch of text on the screen showing Homebrew installing the PostgreSQL files. It’s often a good idea to skim this output even if you don’t understand it as sometimes the output will have very important info. When installing PostgreSQL this is one of those times. Because at the bottom it will give you two commands: pg_ctl -D /usr/local/var/postgres start and brew services start postgresql

There are two major difference between these commands the first one will just start up the postgres server manually. If you ever shut down the computer then you will have to start the server again. The second one will keep Postgres running in the background at all times. This is the option I use, but depending on your computer it may not be best for you. If you just want to manually start and stop the server you’d use the first command above to start it and this one to stop it: pg_ctl -D /usr/local/var/postgres stop. So choose whichever option you’d like and start up your new Postgres server!

Logging in to Postgres

Ok now that your server is up and running you’ll need to login into the Postgres server before you can do anything:


$ psql postgres

The psql command is what logs you into the Postgres database server and postgres is a default database that is created whenever you create/install the Postgres server itself. This database is used by third party programs and it will be the database you connect to run database maintenance commands.

So after you type the above command you should see that your prompt changes to something like this:

psql (10.3, server 10.1)
Type "help" for help.

postgres=#

A couple notes about background things before we go further. When you brew postgres Homebrew does a couple things behind the scenes. It tells you in the text it prints out, but I'm going to highlight them here to make it easier for you. First Homebrew creates a superuser for the Postgres server with the same name as the OS X User you are currently logged in as.

Secondly it sets trust authentication for local connections meaning you don't need to provide a password to log into the data base. Obviously, this is a huge security flaw and if you were going to provide outside access you would change that, but for our purposes in these tutorials we won't get into how to do that.

Ok back to the prompt above. As you can see it tells us what version Postgres we are running, and that we can type help for help. But if you're like me, I find when I'm learning something new those help menus offer no help at all until I get a little knowledge about the system. The next line is where we'll actually put our commands for the Postgres server. That line tells us we are connected to the postgres database and the # informs you that you are logged in as a superuser.

As with my rant in other tutorials about using sudo unless you know what you are doing don't work as a superuser if you can avoid it. So we are going to create another user and give them database creation privileges:

postgres=# CREATE ROLE username WITH LOGIN PASSWORD 'quoted password';

You would change username and 'quoted password' with whatever values you wanted. Next will give our new user the ability to create a database:

postgres=# ALTER ROLE username CREATEDB;

Creating Your First Database

Ok now that we’ve created our new user lets logout of the superuser with \q and log in with your new user with this command psql -U username postgres. The -U flag just tells postgres that you want to login with that particular user and postgres is again just the name of the database. Normally you would be asked for the password you created for the role, but remember that local connections are trusted so you won’t need to enter it.

Ok now you should see a command line that looks like this:

psql (10.3, server 10.1)
Type "help" for help.

postgres=>

As you can see our # has changed to a > indciating that we are not a superuser anymore. So now you’re logged in to your new user lets create a database that we are going to load our data into so we can play around with it:

postgres=> CREATE DATABASE baseballstats;

At this moment I'll point out, if you haven't noticed, that all SQL commands and queries will end with a semicolon. This let's the system know that it has reached the end of the query and there are no more words to parse. If you hit enter without a semicolon a new terminal will pop up with the = changed to a -. This means that Postgres is expecting more commands and it will wait until you pass it a semicolon. This is helpful if you are working with complex queries and you can break them up to make them easier to read, but can be annoying if you are wondering why your commands aren't working.

So to check and make sure our database is created type \l into the terminal. This command will give you a list of all the databases on the Postgres server. Commands beginning with a \, also known as psql commands, don’t need a semicolon and will throw an error if you use one. It is a little confusing but one way to keep it apart is that commands that directly deal with the data itself need semicolons and commands that interact with the actual database don’t. You should see some output similar to this:

                                       List of databases
     Name      |    Owner    | Encoding |   Collate   |    Ctype    |      Access privileges
---------------+-------------+----------+-------------+-------------+-----------------------------
 baseballstats | MattBarlowe | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 postgres      | MattBarlowe | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0     | MattBarlowe | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/MattBarlowe             +
               |             |          |             |             | MattBarlowe=CTc/MattBarlowe
 template1     | MattBarlowe | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/MattBarlowe             +
               |             |          |             |             | MattBarlowe=CTc/MattBarlowe

You should see the baseballstats in the table and that the Owner of the database is the username you created. As Owner of the database that means you have superuser powers over it and that nobody can even read it until you give them acess to the database.

Importing Data to the Database

So we have created our new baseballstats database so lets log into it. Start by typing \q to log out of the postgres database and now we’ll use this command to log into our new database psql -U username baseballstats, (where username is the username you created above) and you’ll see your command prompt is now this:

baseballstats=>

Ok so we have our database running but we don't have any data in yet so that's our next step. Data in a SQL database is stored in different tables. And it is the relationships between these tables that forms the basis of the relational database system I touched on earlier.

The data I will be using for this tutorial and all future tutorials can be downloaded at this link. In this zip file is a PostgreSQL backup of the database I created from the .csv files from Sean Lahman’s baseball database. Once the data is downloaded and unzipped then to load it into the data base all you have to do is this:

psql baseballstats < baseball.bak

And you’ll see a whole bunch of output letting you know the backup is being installed into the database. Once that is all done log back into your database:

psql -U username baseballstats

This will log you into the baseballstats database and lets make sure the data is all in there by doing a simple select:

SELECT * FROM appearances LIMIT 10;

Which should produce output similar to this:

yearID | teamID | lgID | playerID  | G_all | GS | G_batting | G_defense | G_p | G_c | G_1b | G_2b | G_3b | G_ss | G_lf | G_cf | G_rf | G_of | G_dh | G_ph | G_pr
--------+--------+------+-----------+-------+----+-----------+-----------+-----+-----+------+------+------+------+------+------+------+------+------+------+------
  1871 | TRO    |      | abercda01 |     1 |  1 |         1 |         1 |   0 |   0 |    0 |    0 |    0 |    1 |    0 |    0 |    0 |    0 |    0 |    0 |    0
  1871 | RC1    |      | addybo01  |    25 | 25 |        25 |        25 |   0 |   0 |    0 |   22 |    0 |    3 |    0 |    0 |    0 |    0 |    0 |    0 |    0
  1871 | CL1    |      | allisar01 |    29 | 29 |        29 |        29 |   0 |   0 |    0 |    2 |    0 |    0 |    0 |   29 |    0 |   29 |    0 |    0 |    0
  1871 | WS3    |      | allisdo01 |    27 | 27 |        27 |        27 |   0 |  27 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0
  1871 | RC1    |      | ansonca01 |    25 | 25 |        25 |        25 |   0 |   5 |    1 |    2 |   20 |    0 |    1 |    0 |    0 |    1 |    0 |    0 |    0
  1871 | FW1    |      | armstbo01 |    12 | 12 |        12 |        12 |   0 |   0 |    0 |    0 |    0 |    0 |    0 |   11 |    1 |   12 |    0 |    0 |    0
  1871 | RC1    |      | barkeal01 |     1 |  1 |         1 |         1 |   0 |   0 |    0 |    0 |    0 |    0 |    1 |    0 |    0 |    1 |    0 |    0 |    0
  1871 | BS1    |      | barnero01 |    31 | 31 |        31 |        31 |   0 |   0 |    0 |   16 |    0 |   15 |    0 |    0 |    0 |    0 |    0 |    0 |    0
  1871 | FW1    |      | barrebi01 |     1 |  1 |         1 |         1 |   0 |   1 |    0 |    0 |    1 |    0 |    0 |    0 |    0 |    0 |    0 |    0 |    0
  1871 | BS1    |      | barrofr01 |    18 | 17 |        18 |        18 |   0 |   0 |    0 |    1 |    0 |    0 |   13 |    0 |    4 |   17 |    0 |    0 |    0

Thats a lot text and its hard to make sense of, but if you are seeing that means we have great success and your first database is up and running.

If you come up with a ERROR: permission denied for relation appearances when you run the above code you need to login back into your superuser account (which remember is the same name of the OS X you created the postgres server in) into the baseballstats table with this command psql baseballstats and then grant privileges to your username you created above with GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public to username. After that you should be able to select the data normally.

Sources:

Getting Started with PostgreSQL on Mac OSX

Install PostgreSQL on Mac using Brew

How to Start PostgreSQL Server on Mac OSX

Importing and Exporting CSV Files with PostgreSQL

Relational Model

psql Cheatsheet I would definitely bookmark this folks!

Default Postgres Database

Postgres Homebrew Output

Create tables and import CSV