How to install SQL Workbench for postgreSQL

In my previous SQL for data analysis tutorial I’ve briefly mentioned, that I prefer SQL Workbench over pgadmin4 for SQL querying. Today I will show you, how you can install it too! The setup process is more or less the same on Mac, Windows and Linux, but I’ll highlight the slight differences in my article – and you can always select the appropriate solutions for yourself.

STEP 1: Download and install Java 8+ on your computer!

SQL Workbench works with Java8+, so until you don’t have this package, you won’t be able to run it! Java will be useful anyway, so just simply download and install it from here:
https://java.com/en/download

It is fully free – provided by Oracle.

STEP 2: Download SQL Workbench!

It’s free too and you can get it here:
http://www.sql-workbench.net/downloads.html

Make sure you are downloading the latest stable version and also the right version for your operation system:

sql_workbench_install

STEP 3: Open SQL Workbench!

Once you have downloaded your file, unzip it! You don’t have to install anything, just run the SQL Workbench application. If you have trouble opening it:

  • On Windows: right click on SQLWorkbench.exe –» Run as administrator
  • On Mac: right click on SQLWorkbenchJ –» Open

STEP 4: Download the postgreSQL plugin!

Unfortunately you have to download a postgreSQL plugin too!
Link: https://jdbc.postgresql.org/download.html#current

Read the instructions and download the appropriate version (in our case, this is going to be the one that is compatible with Java8+). When I’m writing this article (May, 2017), the right file is: PostgreSQL JDBC 4.2 Driver, 42.1.1

SQL_Workbench_postgresql_driver

Important! Put this file in a folder, where it is safe and where you won’t accidentally move or remove it.

STEP 5: Connect to your server!

Note: if you don’t have your own data server to practice yet, set one up here: Install Python, R, SQL and bash!

When you run your SQL Workbench application, the first window will be the “Connect window”. If not, you can still access it from File –» Connect Window.

It requires a few information to make the connection:

Name: You have to name your connection, which can be anything. (I’ve typed in: “My first postgreSQL connection”)

Driver: Select PostgreSQL! You will get an error message. Something like this:

SQL Workbench error: "The library for the driver is not accessible!"

SQL Workbench error: “The library for the driver is not accessible!”

Click YES!
As you can see, SQL Workbench can’t find the postgreSQL driver by itself… Let’s help it out and click the little Folder icon.

SQL_Workbench_select_driver

Go and find the driver, that you have just downloaded a few minutes ago at STEP 4. Open it!

SQL workbench postgresql-42.1.1.jar driver defined

postgresql-42.1.1.jar is imported…

If you can see it on the “Library list” (like on the above picture), click OK and your driver will be set!

URL: The URL has 4 parts.

sql workbench url

  1. The jdbc:postgresql:// is a constant in our case. You don’t have to change it.
  2. The next part (red) will be the IP address of your server. Type here your server’s IP address (the format should be something like: 46.101.100.000)
  3. The next step (green) is the port number. If you followed my tutorials so far, this is going to be 5432 for you as well!
  4. Finally provide the name of the database that you want to access (it can be anything, that has been created before… If you followed my tutorials so far, postgres will be available for sure!)

Note: usually when you get access to a database, you get these information (IP address, port, database name) from the admin of the database.

Username: This should be the username, that you set up, when you have installed your data server. (eg. for me it’s “dataguy” or “tomi”)

Password: Your previously set password.

If you did everything properly, you should have something like this on your screen:

SQL workbench ready to login

Click OK and the connection will be established!
Congrats! You are in!

Note: It’s worth to save your connection details, so you don’t have to type it again!

STEP 6 – Run a test query!

As a last step, just run some sample queries to test out your new SQL query tool!
You can try the classy SELECT * FROM zoo; or you can go ahead and explore your data tables via the Database Explorer feature:

SQL explore

SQL database explorer

Conclusion

Aaand… That’s it for today! You have a cool free SQL query tool, SQL Workbench. From now on you can use either this or pgadmin4 to complete my SQL for data analysis tutorials!

And if you want to be notified first about new content on data36 blog (like articles, videos, live webinars, etc.), sign up for the Newsletter!

Cheers,
Tomi Mester

← Previous post

Next post →

2 Comments

  1. Hey this is a great article, however, after using the SQL Workbench interface, it is not very user friendly. Is there a reason to not use pgAdmin? Seems much easier to use, and no need to download extra plugins to get it to work.

    • hi Nitin,

      thanks and fair point. Some might prefer pgAdmin, I prefer SQL Workbench, but feel free to use any of the SQL tools you like! 🙂

      Cheers,
      Tomi

Leave a Reply