EDUCBA

EDUCBA

MENUMENU
  • Blog
  • Free Courses
  • All Courses
  • All in One Bundle
  • Login
Home Data Science Data Science Tutorials PostgreSQL Tutorial PostgreSQL FETCH

PostgreSQL FETCH

By Sohel SayyadSohel Sayyad

PostgreSQL FETCH

Introduction to PostgreSQL FETCH

The PostgreSQL FETCH clause retrieves the portion of rows returned by a statement from a cursor. The FETCH clause is SQL-standard which has been introduced in SQL:2008. The PostgreSQL FETCH clause uses the previous cursor to limit the number of rows from the result set fetched from a statement.

Syntax:

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

OFFSET start { ROW | ROWS }
FETCH
{ FIRST | NEXT }
[ count ]
{ ROW | ROWS }
ONLY

Explanation:

  • FIRST or NEXT / ROW or ROWS: FIRST and ROWS are similar terms with NEXT and ROWS, respectively.
  • Start: It is an integer value that should be zero or positive. If the OFFSET clause is defined, then the default value of the Start is zero. The query returns nothing if the value of the Start is greater than the result set.
  • Count: The value of the count is one or higher. Suppose the count is defined, then its value defaulter to one.

How does the FETCH clause work in PostgreSQL?

  • The PostgreSQL FETCH clause helps us to fetch the specified number of rows using a cursor.
  • While using a cursor user needs to be in a transaction; as a result, it is not independent of other users within the same system.
  • We can specify the row count as negative or positive. The positive row count will retrieve as per the direction parameter defined. The default direction will be FORWARD if we have not specified anything.
  • To make sure the order of the SQL statement is consistent, you need to use the ORDER BY clause while using the FETCH clause.

Examples

Let’s create a table named ’COUNTRIES’ to understand the examples.

Example #1

The following CREATE TABLE statements will create the COUNTRIES table.

Code:

CREATE table COUNTRIES
(
country_id serial PRIMARY KEY,
country_name VARCHAR (256) NOT null,
country_code numeric NOT NULL
);

Now insert some data into the COUNTRIES table using the INSERT statement as follows:

Code:

INSERT INTO COUNTRIES (country_name,country_code)
VALUES
('Nepal', 977),
('Afghanistan', 93),
('Barbados', 1),
('Oman', 968),
('India', 91),
('Japan', 81),
('Kenya', 254),
('Kuwait', 965),
('Malaysia', 60),
('Ukraine', 380);

Code:

select * from COUNTRIES;

Output:

PostgreSQL FETCH - 1

Example #2

The following statement will return us to the first row of the ‘countries’ sorted by country_name:

Code1:

SELECT
country_id,
country_name
FROM
countries
ORDER BY
country_name
FETCH FIRST ROW ONLY;

Output:

countries

Code2:

SELECT
country_id,
country_name
FROM
countries
ORDER BY
country_name
FETCH FIRST 1 ROW ONLY;

Output:

PostgreSQL FETCH - 3

The above statement is similar to the following one: both statements will return the same result.

Example #3

The following statement will return us to the first seven rows of the countries sorted by country_name:

Code:

SELECT
country_id,
country_name
FROM
countries
ORDER BY
country_name
FETCH FIRST 7 ROW ONLY;

Output:

PostgreSQL FETCH - 4

Example #4

The following statement will return us to the next three rows of the countries after the first three rows of the countries sorted by country_name:

Code:

SELECT
country_id,
country_name
FROM
countries
ORDER BY
country_name
OFFSET 3 ROWS
FETCH FIRST 3 ROW ONLY;

Output:

sorted by country_name:

Advantages of using FETCH in PostgreSQL

  • We can use the LIMIT clause to limit the number of rows a statement returns. But the LIMIT clause is not a SQL-standard. So we should prefer The PostgreSQL FETCH clause, which is as per the standard SQL introduced in SQL:2008.
  • The PostgreSQL FETCH clause uses a previously created cursor for fetching the rows.

Conclusion

From the above article, we hope you have learned about the PostgreSQL FETCH clause and how the PostgreSQL FETCH works. Also, we have added some examples to demonstrate to you how to use the FETCH clause.

Recommended Articles

We hope that this EDUCBA information on “PostgreSQL FETCH” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. PostgreSQL String Functions
  2. The Sequence in PostgreSQL | How to Work?
  3. PostgreSQL Schema | How to Create?
  4. Guide to PostgreSQL OFFSET
PROGRAMMING LANGUAGES Course
502+ Hours of HD Videos
54 Courses
4 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
SELENIUM Certification Course
57+ Hours of HD Videos
15 Courses
9 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
IOT System - Design & Develop an IOT System
65+ Hours of HD Videos
7 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
JENKINS Certification Course
19+ Hours of HD Videos
6 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
Primary Sidebar
Popular Course in this category
POSTGRESQL Certification Course
 17+ Hour of HD Videos
4 Courses
1 Mock Tests & Quizzes
  Verifiable Certificate of Completion
  Lifetime Access
4.5
Price

View Course
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more