How to Use FETCH FIRST in PostgreSQL: Syntax and Examples

In PostgreSQL, use FETCH FIRST n ROWS ONLY at the end of a SELECT query to limit the number of rows returned to n. It works like LIMIT but follows the SQL standard syntax.

Syntax

The basic syntax for using FETCH FIRST in PostgreSQL is:

• SELECT columns FROM table: Choose columns and table.
• ORDER BY column: Optional, to sort results before limiting.
• FETCH FIRST n ROWS ONLY: Limits the output to n rows.

This syntax is part of the SQL standard and is an alternative to LIMIT.

sql

SELECT column1, column2
FROM table_name
ORDER BY column1
FETCH FIRST n ROWS ONLY;

Example

This example shows how to select the first 3 rows from a table called employees, ordered by salary descending:

sql

SELECT id, name, salary
FROM employees
ORDER BY salary DESC
FETCH FIRST 3 ROWS ONLY;

Output

 id |   name    | salary 
----+-----------+--------
  7 | Alice     | 90000  
  3 | Bob       | 85000  
 12 | Charlie   | 80000  

Common Pitfalls

Common mistakes when using FETCH FIRST include:

• Omitting ORDER BY, which can return unpredictable rows.
• Confusing FETCH FIRST with LIMIT syntax; both work but have different standards.
• Using FETCH FIRST without specifying ROWS ONLY or PERCENT, which can cause errors.

Correct usage always includes ROWS ONLY or PERCENT after the number.

sql

/* Wrong: Missing ROWS ONLY */
SELECT * FROM employees FETCH FIRST 5 ROWS;

/* Right: Include ROWS ONLY */
SELECT * FROM employees FETCH FIRST 5 ROWS ONLY;

Quick Reference

Clause	Description	Example
FETCH FIRST n ROWS ONLY	Limits output to n rows	FETCH FIRST 10 ROWS ONLY
FETCH FIRST n PERCENT ROWS ONLY	Limits output to n percent of rows	FETCH FIRST 50 PERCENT ROWS ONLY
ORDER BY	Sorts rows before limiting	ORDER BY created_at DESC
OFFSET n	Skips first n rows	OFFSET 5

Key Takeaways

Use FETCH FIRST n ROWS ONLY to limit query results in PostgreSQL following SQL standard.

Always include ORDER BY to control which rows are returned first.

FETCH FIRST requires ROWS ONLY or PERCENT to be valid syntax.

FETCH FIRST is an alternative to LIMIT with similar behavior.

Avoid omitting ORDER BY to prevent unpredictable results.