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:
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