In this tutorial, we will be learning about the FETCH clause in PostgreSQL. The FETCH clause is very similar to the LIMIT clause, therefore if you know how LIMIT works, then you will understand the FETCH clause very easily. So without further ado, let’s get started!
Introduction to PostgreSQL FETCH Clause
As said earlier, the FETCH clause is very similar to the LIMIT clause. It is used to limit the returned rows by the query to a specific number. For example, if you want only the first 5 products from the table, you can use the FETCH clause.
Many relational database management systems offer the LIMIT clause such as MySQL, PostgreSQL etc. However, the LIMIT clause is not a SQL standard, whereas the FETCH clause is. Therefore, to comply with the SQL standards, PostgreSQL supports the FETCH clause.
Now let’s see the syntax of the FETCH clause in PostgreSQL.
Syntax of PostgreSQL FETCH Clause
The following illustrates the syntax of the FETCH clause in PostgreSQL.
Syntax:
OFFSET start { ROW | ROWS }
FETCH { FIRST | NEXT } [ row_count ] { ROW | ROWS } ONLYCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)Here,
- OFFSET will skip the number of rows specified.
- start is the number of rows to skip. It must be greater than or equal to 0. If this number is greater than the number of rows in the result set, no rows will be returned.
- ROW/ROWS are synonyms for each other, therefore you can use any.
- FIRST/NEXT are synonyms to each other too, use any.
- row_count is the number of rows you want to limit. The number of rows in the output will be less than or equal to the row_count depending upon the number of records available in the table. The row_count must be greater than or equal to 1.
Now that we are familiar with the FETCH syntax, let’s move forward to see some examples.
Examples of PostgreSQL FETCH Clause
To demonstrate the FETCH clause examples, we will need a table and some sample data. So, let’s create a table first.
DROP TABLE IF EXISTS employees;
CREATE TABLE employees (
employee_id serial PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
department VARCHAR(50),
salary numeric
);
INSERT INTO employees (first_name, last_name, department, salary)
VALUES
('John', 'Doe', 'HR', 50000),
('Jane', 'Smith', 'Marketing', 55000),
('Bob', 'Johnson', 'IT', 60000),
('Alice', 'Brown', 'Finance', 52000),
('Charlie', 'Wilson', 'Sales', 48000),
('Eva', 'Davis', 'IT', 58000),
('Frank', 'Miller', 'Marketing', 54000);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)Output:
perfect! Let’s now move forward.
Example 1: Fetch a Single Row
Let’s say you want to display the first employee when you sort them by their salary in ascending order. That means you want to see the employee with the lowest salary.
SELECT * FROM employees
ORDER BY salary
FETCH FIRST ROW ONLY;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)Output:
Perfect! As you can see, we have got the least earning employee.
Example 2: Fetch First N Rows
Now you want to see the top three most salaried employees. In this case, we will sort the employees by their salaries in descending order and fetch only the first 3 rows.
SELECT * FROM employees
ORDER BY salary DESC
FETCH FIRST 3 ROWS ONLY;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)Output:
As you can see, we have got the expected output.
Note that, the keywords ROW and ROWS work exactly the same. You can use any of them, but it is advised to use the ROWS when you are fetching multiple rows for better code-understanding purposes.
Example 3: Use OFFSET with FETCH
To demonstrate the OFFSET and the FETCH in a single query, we will take a very simple example. Let’s say you want to fetch the first 3 most salaried employees but you want to skip the first employee.
SELECT * FROM employees
ORDER BY salary DESC
OFFSET 1
FETCH FIRST 3 ROWS ONLY;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)Output:
Here you can see, the most salaried employee from the previous query is skipped in this query because we have used the OFFSET keyword. To understand how we got this output, let’s break down the execution.
Without FETCH and OFFSET, we will get all the rows from the table which are sorted by the salary in descending order. Next, the OFFSET will skip the first row from the result set and the FETCH clause will apply the limit on the remaining rows. Therefore, we will get the next 3 rows.
Note that, you can use the NEXT keyword in place of FIRST, both work the exactly same.
Read More: A Guide to LIMIT and OFFSET With Examples
Conclusion
In this tutorial, we have learned about the FETCH clause which works exactly the same as the LIMIT clause. However, you should always prefer the FETCH clause because it is an SQL standard. Another thing worth noticing is, that you must use the ORDER BY clause in order to get the predicted output because if you don’t use the ORDER BY clause, the output will be inconsistent every other time. Happy learning!