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.
