Getting started with PostgreSQL

Introduction

PostgreSQL is one of the largest open source SQL database engines. In this article I’m going to do a quick run through on installing and interacting with it.

After installation we will be using command line to install a sample database and then explore some essential SQL Shell (psql) commands.

This post follows installing PostgreSQL on Windows 10.

Installation

You can install the latest version of PostgreSQL from their sites download page. I selected Windows x86-64 for this blog post. I have left the default directory entered and ensured that Command Line Tools will be installed.

Again I’ve used a default directory for the data folder and entered an admin password.

Install Sample Database

Now we have our database engine installed we’re going to need a sample database to work with. You can download a sample database of your choice from Harry Moreno’s GitHub repo here. I’ll be creating the Adventure Works database.

In his repo he provides a SQL install script and the data in CSV files zipped that need to be referenced in a relative path to the install script. So I have downloaded both and put them on my C drive in a folder named psdata.

We also need to make sure that psql is set up as an Environment variable on our Windows 10 machine. If it is not then you can follow this post on Stack Overflow.

Command line to create the database.

psql -h localhost -U postgres -a -c "CREATE DATABASE adventureworks;"

Command line to run the sql install file.

psql -h localhost -d adventureworks -U postgres -a -f "C:\psdata\install.sql"

After running this line you should hopefully see your terminal light up as it runs through the SQL file commands. At the end of the script it will output a list of installed objects like below.

SQL Shell (psql) Commands

Here we’re going to run through some useful lines using the SQL Shell we installed earlier. Hit the windows key and search psql.

List all databases 

To list all databases you can use the \l command.

 You’ll see by default the system databases exist along side our sample database “adventureworks”.

Switch between databases 

To switch the context of a database you can use the following command \c <database_name> 

List all schemas

To list the schemas available run \dn+

List tables in a database 

Using the schemas we’ve found we can then list the tables in those schemas with…

\dt (humanresources|person|production|purchasing|sales).*

Show a tables meta data

\d sales.store

Run SQL commands

SELECT businessentityid, name, salespersonid FROM sales.store LIMIT 10;

Conclusion

In this short post we’ve learnt how to quickly install and interact with PostgreSQL. You can also try using the graphical interface pgAdmin. Feel free to leave any comments if you get stuck with the sample database install, otherwise I hope this has been helpful.