{"id":459,"date":"2024-11-28T15:43:27","date_gmt":"2024-11-28T08:43:27","guid":{"rendered":"https:\/\/www.pgtutorial.com\/?page_id=459"},"modified":"2025-01-22T10:05:52","modified_gmt":"2025-01-22T03:05:52","slug":"postgresql-fetch","status":"publish","type":"page","link":"https:\/\/www.pgtutorial.com\/postgresql-tutorial\/postgresql-fetch\/","title":{"rendered":"PostgreSQL FETCH"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the PostgreSQL FETCH<\/code> clause to retrieve a subset of rows from a query.<\/p>\n\n\n\n

Introduction to PostgreSQL FETCH clause #<\/a><\/h2>\n\n\n\n

In PostgreSQL, the OFFSET<\/code>clause works like the LIMIT<\/a><\/code>clause. The FETCH<\/code> clause allows you to limit the number of rows to return from a query.<\/p>\n\n\n\n

The LIMIT<\/code> clause is not a part of the SQL standard. However, the FETCH<\/code> clause is a part of the SQL:2008 standard. If you want your application to support other databases in the future, use FETCH<\/code> instead of LIMIT<\/code> because other database vendors will likely support it.<\/p>\n\n\n\n

Here’s the syntax of the OFFSET FETCH<\/code> clause:<\/p>\n\n\n

SELECT<\/span> column1, column2\nFROM<\/span> table_name<\/span>\nORDER<\/span> BY<\/span> sort_expression\nOFFSET<\/span> skip_count\nFETCH<\/span> { FIRST | NEXT } ] [row_count<\/span>] {ROW<\/span> | ROWS<\/span> } ONLY<\/span>;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax:<\/p>\n\n\n\n
First, specify the name of the table you want to retrieve data in the FROM<\/code> clause.<\/p>\n\n\n\n
Second, restrict which columns from the rows to include in the final result set in the SELECT<\/code> clause.<\/p>\n\n\n\n
Fourth, provide the number of rows to skip in the OFFSET<\/code> clause before the FETCH<\/code> clause returns a subset of rows:<\/p>\n\n\n
OFFSET<\/span> skip_count<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
The skip_count<\/code> determines the number of rows to skip. It can be a zero or a positive integer. If skip_count<\/code> is zero, the query will not skip any rows.<\/p>\n\n\n\n
The OFFSET<\/code> clause is optional. If you omit it, the query will also not skip any rows.<\/p>\n\n\n\n
Fifth, set the number of rows to return (row_count<\/code>) in the FETCH<\/code> clause.<\/p>\n\n\n
FETCH<\/span> { FIRST | NEXT } ] [row_count<\/span>] {ROW<\/span> | ROWS<\/span> } ONLY<\/span>;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
The row_count<\/code> defaults to 1, meaning that if you omit it, the FETCH<\/code> will return one row from the query.<\/p>\n\n\n\n
You can use FIRST<\/code> and NEXT<\/code>, ROW<\/code>, and ROWS<\/code> interchangeably because they are synonyms.<\/p>\n\n\n\n
For example:<\/p>\n\n\n
FETCH FIRST<\/span> 1<\/span> ROW<\/span> ONLY<\/span>;\nFETCH FIRST<\/span> ROW<\/span> ONLY<\/span>;\nFETCH FIRST<\/span> 10<\/span> ROWS<\/span> ONLY<\/span>;\nFETCH NEXT<\/span> 1<\/span> ROW<\/span> ONLY<\/span>;\nFETCH NEXT<\/span> ROW<\/span> ONLY<\/span>;\nFETCH NEXT<\/span> 10<\/span> ROWS<\/span> ONLY<\/span>;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Setting up a sample table #<\/a><\/h2>\n\n\n\n
We’ll use the products<\/code> table created in the LIMIT<\/code> tutorial to practice with the FETCH<\/code> clause.<\/p>\n\n\n\n
Here’s the SQL script to create the products table and insert some rows.<\/summary>
CREATE<\/span> TABLE<\/span> products (\n  product_id INT<\/span> GENERATED<\/span> ALWAYS<\/span> AS<\/span> IDENTITY<\/span> PRIMARY KEY<\/span>,\n  name<\/span> VARCHAR<\/span>(255<\/span>) NOT<\/span> NULL<\/span>,\n  price DECIMAL<\/span>(10<\/span>, 2<\/span>)\n);\n\nINSERT<\/span> INTO<\/span>\n  products (name<\/span>, price)\nVALUES<\/span>\n  ('iPhone 16 Pro Max'<\/span>, 1649.00<\/span>),\n  ('iPhone 16'<\/span>, 829.00<\/span>),\n  ('Galaxy S24 Ultra'<\/span>, 1299.99<\/span>),\n  ('Galaxy S24 FE'<\/span>, 949.99<\/span>),\n  ('Pixel 9 Pro'<\/span>, 799.00<\/span>),\n  ('Pixel 8a'<\/span>, 399.00<\/span>),\n  ('OnePlus 12'<\/span>, 745.00<\/span>),\n  ('OnePlus Open'<\/span>, 1514.99<\/span>),\n  ('Galaxy Z Fold 6'<\/span>, 2019.99<\/span>);<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre><\/details>\n\n\n\n
product_id<\/th>name<\/th>price<\/th><\/tr><\/thead>
1<\/td>iPhone 16 Pro Max<\/td>1649.00<\/td><\/tr>
2<\/td>iPhone 16<\/td>829.00<\/td><\/tr>
3<\/td>Galaxy S24 Ultra<\/td>1299.99<\/td><\/tr>
4<\/td>Galaxy S24 FE<\/td>949.99<\/td><\/tr>
5<\/td>Pixel 9 Pro<\/td>799.00<\/td><\/tr>
6<\/td>Pixel 8a<\/td>399.00<\/td><\/tr>
7<\/td>OnePlus 12<\/td>745.00<\/td><\/tr>
8<\/td>OnePlus Open<\/td>1514.99<\/td><\/tr>
9<\/td>Galaxy Z Fold 6<\/td>2019.99<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
Fetching the first row #<\/a><\/h2>\n\n\n\n
The following statement uses the FETCH<\/code> clause to retrieve the most expensive product from the products<\/code> table:<\/p>\n\n\n
SELECT<\/span>\n  name<\/span>,\n  price\nFROM<\/span>\n  products\nORDER<\/span> BY<\/span>\n  price DESC<\/span>\nFETCH FIRST<\/span> ROW<\/span> ONLY<\/span>;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
      name<\/span>       |  price\n-----------------+---------<\/span>\n Galaxy Z Fold 6<\/span> | 2019.99<\/span><\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Fetching some rows #<\/a><\/h2>\n\n\n\n
The following example uses the FETCH<\/code> clause to retrieve the top three most expensive products from the products<\/code> table:<\/p>\n\n\n
SELECT<\/span>\n  name<\/span>,\n  price\nFROM<\/span>\n  products\nORDER<\/span> BY<\/span>\n  price DESC<\/span>\nFETCH FIRST<\/span> 3<\/span> ROWS<\/span> ONLY<\/span>;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
       name<\/span>        |  price\n-------------------+---------<\/span>\n Galaxy Z Fold 6<\/span>   | 2019.99<\/span>\n iPhone 16<\/span> Pro Max | 1649.00<\/span>\n OnePlus Open<\/span>      | 1514.99<\/span><\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Skipping some rows before fetching #<\/a><\/h2>\n\n\n\n
The following statement uses the OFFSET<\/code> and FETCH<\/code> clauses to get the second most expensive products from the products<\/code> table:<\/p>\n\n\n
SELECT<\/span>\n  name<\/span>,\n  price\nFROM<\/span>\n  products\nORDER<\/span> BY<\/span>\n  price DESC<\/span>\nOFFSET<\/span> 1<\/span>\nFETCH NEXT<\/span> 1<\/span> ROW<\/span> ONLY<\/span>;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
       name<\/span>        |  price\n-------------------+---------<\/span>\n iPhone 16<\/span> Pro Max | 1649.00<\/span><\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Using PostgreSQL FETCH clause for pagination #<\/a><\/h2>\n\n\n\n
The FETCH<\/code> clause can be helpful for pagination:<\/p>\n\n\n
OFFSET<\/span> record_per_page * (page_no - 1<\/span>)\nFETCH NEXT<\/span> record_per_page ROWS<\/span> ONLY<\/span>.<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax:<\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Third, use the ORDER BY<\/a><\/code> clause to sort the rows by values in one or more columns.<\/p>\n\n\n\n