PostgreSQL SELECT First Record on an ObjectRocket Instance

Introduction

When you perform a typical PostgreSQL query, you can expect to have the entire result set returned. However, that may not be exactly what you need. What if you wanted to select just the first record of the result set? With the help of the LIMIT clause, it’s possible to specify how many rows are returned from a PostgreSQL query. In this article, we’ll show you how to use PostgreSQL to SELECT the first record on an ObjectRocket instance.

Prerequisites

Before you attempt to follow along with the instructions in this article, make sure that these two prerequisites are in place:

• PostgreSQL 11 should be installed on your machine.

• You’ll need an instance of Postgres created for your ObjectRocket account. You can create it using the Mission Control panel.

Connect to PostgreSQL Instance

Our first task will be to connect to PostgreSQL on our ObjectRocket instance. This can be done using the psql utility with the following connection details:

We can also use the command shown below in a terminal window:

PostgreSQL SELECT Statement

The Postgres SELECT statement enables us to retrieve data from a target table that resides within the database. Data is returned in a result table, which is also known as the result set.

Shown below is the syntax for a Postgres SELECT statement:

First, we use the SELECT keyword, followed by the name of the column in the table from which we want to retrieve data. If we want to query multiple columns, we simply use a comma to separate them. Using an asterisk instead of specifying column names will return all columns in the table. After we specify our desired columns, we specify the name of the targeted table in our FROM clause.

Sample Data Set

We’ll be using the following sample data set for demo purposes throughout this tutorial:

PostgreSQL SELECT the First Record Using LIMIT Clause Example

In this example, we’ll show you how to retrieve the first record from the table using the combination of a SELECT statement and LIMIT clause:

Notice that we are selecting all columns within the president table; however, we are limiting the result set to just one record because of the LIMIT clause.

The result should look like this:

PostgreSQL Select First Record Using WHERE Clause Example

Our next example will provide the same result as the previous query but with a different query structure:

This query selects the first record in the table with an id that has a value of 1.

The output should look like this:

PostgreSQL Select the First Batch of Rows Example

In the previous section, we selected the first single record from a result set. Now, let’s select the first batch of records. The number of records in the batch depend on the specified criteria:

We use the FETCH statement, which provides functionality similar to the LIMIT keyword.

The result should look like the following:

Conclusion

If you need to select just the first record of a query’s result set, PostgreSQL offers a simple way to get the job done. Adding a LIMIT clause to your SELECT statement allows you to specify the number of results you’d like to have returned from your query. In this article, we showed you how to use PostgreSQL to select the first record on an ObjectRocket instance. By the time you’ve completed this tutorial, you’ll be ready to perform this type of query in your own PostgreSQL environment.