SQL BACKWARD

What is the SQL BACKWARD keyword in PostgreSQL?

Specifies reverse scrolling direction when fetching or moving within a PostgreSQL cursor.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL BACKWARD: PostgreSQL (7.3+) only

SQL BACKWARD Full Explanation

BACKWARD is a direction modifier used with the FETCH and MOVE statements in PostgreSQL. It tells the database to retrieve or skip rows in the reverse order of the cursor’s current position. BACKWARD is only valid for scrollable cursors (those declared with the SCROLL option or implicitly scrollable). If the cursor is declared NO SCROLL, attempting to fetch BACKWARD raises an error. When no count is supplied, PostgreSQL defaults to 1 row. A count of ALL or a positive integer indicates how many rows to move or return in reverse. BACKWARD is non-standard SQL and is not available in most other database systems. Cursors are bound to the session and transaction in which they are created, so cursor operations—including BACKWARD—must run inside the same transaction.

SQL BACKWARD Syntax

-- Fetch rows in reverse order
FETCH BACKWARD [count | ALL] FROM cursor_name;

-- Move the cursor pointer backward without returning rows
MOVE BACKWARD [count | ALL] FROM cursor_name;

SQL BACKWARD Parameters

cursor_name (identifier) - The name of an open cursor.
count (integer) - Optional. Number of rows to move or return (defaults to 1). Use ALL for the entire remaining set.

Example Queries Using SQL BACKWARD

BEGIN;
DECLARE emp_cur SCROLL CURSOR FOR
  SELECT id, name FROM employees ORDER BY id;

-- Read first 5 rows normally
FETCH FORWARD 5 FROM emp_cur;

-- Step back 1 row and display it
FETCH BACKWARD FROM emp_cur;

-- Skip two rows backward without returning them
MOVE BACKWARD 2 FROM emp_cur;

-- Show the current row after the move
FETCH FORWARD 1 FROM emp_cur;

CLOSE emp_cur;
COMMIT;

Expected Output Using SQL BACKWARD

FETCH BACKWARD returns the requested rows in reverse order and sets the cursor position accordingly
MOVE BACKWARD adjusts the cursor position without producing a result set other than a row-count notice

Use Cases with SQL BACKWARD

Interactive data browsing tools that let users page back and forth through result sets.
Report generators that must revisit previous rows after look-ahead logic.
Debugging sessions where developers need to inspect earlier rows without re-executing the query.

Common Mistakes with SQL BACKWARD

Using BACKWARD on a cursor declared NO SCROLL, which raises ERROR: cursor can only scan forward.
Forgetting to open a transaction before declaring the cursor, leading to an implicit commit that closes the cursor.
Assuming BACKWARD is portable to MySQL, SQL Server, or Oracle—it is PostgreSQL-specific.
Expecting count to accept negative numbers; only positive integers or ALL are valid.

First Introduced In

PostgreSQL 7.3

Frequently Asked Questions

What is the difference between BACKWARD and PRIOR?

PRIOR is a synonym for BACKWARD 1. Using BACKWARD without a count or using PRIOR returns the single previous row.

Does BACKWARD work outside a transaction block?

No. Cursors are closed at transaction end. Always wrap DECLARE, FETCH, MOVE, and CLOSE in the same transaction.

Can I fetch all remaining rows backward?

Yes. Use FETCH BACKWARD ALL FROM cursor_name; to retrieve every row from the current position to the start.

Is BACKWARD supported in PL/pgSQL loops?

Absolutely. You can FETCH BACKWARD inside a PL/pgSQL loop to iterate through a result set in reverse.