PostgreSQL Join for ObjectRocket Instance

Introduction

In this tutorial we’ll be covering how to do Joins using a PostgreSQL database hosted on the ObjectRocket platform. There are endless ways to use Joins to gather data from different tables and we’ll be showing some basic examples of that. We’ll cover JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN in this article with examples of each. Now let’s dive in and cover how to do a PostgreSQL Join on an ObjectRocket instance.

Prerequisites

Before you proceed with the examples for the PostgreSQL JOIN clause for your ObjectRocket instance, you will need to have the following:

ObjectRocket Instance of PostgreSQL

Set up a PostgreSQL instance on your ObjectRocket account in the create instance tab.

Connection of PostgreSQL to the database

You can use the command prompt and use the psql command or the SQL Shell (psql), if you are using a Windows operating system.

The following is an example of using the SQL Shell (psql) to connect to the ObjectRocket instance of PostgreSQL:

You can use the psql command on the terminal window to connect to the ObjectRocket instance, if you are using a UNIX-based operating system.

The following is an example of using the UNIX terminal window to connect to the ObjectRocket instance of PostgreSQL:

NOTE: Make sure to change the port and ingress URI from the above example so that it matches your instance. Check the CONNECT tab for your instance in Mission Control to get your Postrges instance’s URL and port information.

PostgreSQL join clause

The PostgreSQL join is used to combine the columns of multiple tables into one.

Joins supported by PostgreSQL

PostgreSQL create table example

Let us first create two tables that we will use on our examples.

The following will create a tables named 'varsity_players' and 'top_students':

Postgres update & join examples

Now let us insert some records into our tables so we have some data to work with.

The following commands will insert several records into our tables 'varsity_players' and 'top_students':

Now that we have tables with some data let’s continue with our examples.

PostgreSQL inner join examples

For this example, we will list the varsity players that are also top students by using the INNER JOIN, see the following:

Notice how the data from the two tables is combined:

PostgreSQL left join

For this example, we will use a LEFT JOIN FROM the varsity_players with the top_students on a name match. This LEFT JOIN will guarantee that all the varsity_players will be listed and will only populate the top_students data if there is a match on name.

PostgreSQL right join

For this example, we will list all the top_students and the varsity_players that are top_students as well using the RIGHT JOIN, see the following:

The above will return the following table:

Notice how the IDs from the top_students table are joined with the varsity_players table’s column records.

PostgreSQL full outer join

For this example, we will list all the varsity players and all the top students using the FULL OUTER JOIN, see the following:

The above statement will match up and combine the name columns for each table and will look the following:

Conclusion

We hope you’ve found this article on how to perform a PostgreSQL join on the ObjectRocket platform informative. We covered the most common joins and showed examples of each so you can see which type suits your situation. If you don’t have a database already, getting your free PostgreSQL database setup in ObjectRocket is easy and it’s just as easy to connect to.