PostgreSQL SELECT First Record on an ObjectRocket Instance

Have a Database Problem? Speak with an Expert for Free
Get Started >>

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:

1
2
3
4
5
Server [localhost]: ingress.w98sujpz.launchpad.objectrocket.cloud
DATABASE [PostgreSQL]: <your_database_name>
Port [5432]: 4149
Username [PostgreSQL]: pguser
Password FOR USER orkb: <your_password>

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

1
psql -h ingress.w98sujpz.launchpad.objectrocket.cloud -U pguser -d PostgreSQL -p 4149

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:

1
2
SELECT <column_name OR list_of_column>
FROM <table_name>

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:

1
2
3
4
5
6
7
8
9
10
11
12
13
 id |        name        |      year
----+--------------------+----------------
  1 | Rorigo Duterte     | 2016 - Present
  2 | Benigno Aquino     | 2010 - 2016
  3 | Gloria Arroyo      | 2001 - 2010
  4 | Joseph Estrada     | 1998 - 2001
  5 | Fidel Ramos        | 1992 - 1998
  6 | Corazon Aquino     | 1986 - 1992
  7 | Ferdinand Marcos   | 1965 - 1986
  8 | Diosdado Macapagal | 1961 - 1965
  9 | Carlos Garcia      | 1957 - 1961
 10 | Ramon Magsaysay    | 1953 - 1957
(10 rows)

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:

1
SELECT * FROM presidents LIMIT 1;

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:

1
2
3
4
 id |      name      |      year
----+----------------+----------------
  1 | Rorigo Duterte | 2016 - Present
(1 row)

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:

1
SELECT * FROM president WHERE id = 1;

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

The output should look like this:

1
2
3
4
 id |      name      |      year
----+----------------+----------------
  1 | Rorigo Duterte | 2016 - Present
(1 row)

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:

1
SELECT * FROM presidents FETCH FIRST 5 ROW ONLY;

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

The result should look like the following:

1
2
3
4
5
6
7
8
 id |      name      |      year
----+----------------+----------------
  1 | Rorigo Duterte | 2016 - Present
  2 | Benigno Aquino | 2010 - 2016
  3 | Gloria Arroyo  | 2001 - 2010
  4 | Joseph Estrada | 1998 - 2001
  5 | Fidel Ramos    | 1992 - 1998
(5 rows

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.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.