SQL MOVE

What is the SQL MOVE command?

MOVE changes the position of a database cursor without returning any rows.

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 MOVE: PostgreSQL: Supported Other major databases: Not supported (Oracle, SQL Server, MySQL, SQLite use different cursor mechanisms)

SQL MOVE Full Explanation

MOVE is a cursor-control command in PostgreSQL that shifts the cursor’s position within its result set but does not return the moved-over rows to the client. It shares the same direction options as FETCH (NEXT, PRIOR, FIRST, LAST, ABSOLUTE n, RELATIVE n, FORWARD/BACKWARD [count|ALL]). Because no data are transmitted, MOVE is faster than FETCH when the application only needs to reposition the cursor. MOVE can be issued inside explicit transactions or PL/pgSQL code that has an open cursor. Attempting to MOVE a non-existent cursor, or moving beyond the available rows, raises a warning but not an error. MOVE respects the SCROLL attribute declared on the cursor; non-scrollable cursors can only MOVE FORWARD.

SQL MOVE Syntax

MOVE [ direction { FROM | IN } ] cursor_name;

-- direction defaults to NEXT when omitted

SQL MOVE Parameters

direction (optional) - keyword - Specifies how many rows and in which direction to move. Valid values: NEXT, PRIOR, FIRST, LAST, ABSOLUTE count, RELATIVE count, FORWARD/BACKWARD [count|ALL]
FROM | IN (optional) - keyword - Noise words accepted for SQL conformance
cursor_name (identifier) - The name of an open cursor

Example Queries Using SQL MOVE

BEGIN;
DECLARE user_cur CURSOR FOR SELECT id, email FROM users ORDER BY id;

-- Skip the first 100 rows quickly
MOVE FORWARD 100 IN user_cur;

-- Fetch the next row after the skip
FETCH NEXT FROM user_cur;

-- Jump back 10 rows
MOVE BACKWARD 10 FROM user_cur;

CLOSE user_cur;
COMMIT;

Expected Output Using SQL MOVE

Each MOVE returns a command tag like "MOVE 100" indicating how many rows were skipped
No row data are returned to the client

Use Cases with SQL MOVE

Skipping over large blocks of rows when paginating through a cursor
Repositioning a scrollable cursor before the next FETCH
Implementing custom navigation (next, previous, jump) in stored procedures without incurring network overhead

Common Mistakes with SQL MOVE

Using MOVE on a cursor that was declared NO SCROLL (only FORWARD moves are allowed)
Expecting MOVE to return row data; it never does
Forgetting to wrap cursor operations inside a transaction block
Attempting to MOVE after the cursor has been closed

First Introduced In

PostgreSQL 7.3

Frequently Asked Questions

What is the difference between MOVE and FETCH?

MOVE repositions the cursor and returns only a row count, while FETCH repositions the cursor and returns the actual row data that were skipped or reached.

Can I use MOVE outside a transaction?

No. Cursors exist only within an open transaction block. Issue BEGIN first, then DECLARE, MOVE, FETCH, and CLOSE before COMMIT/ROLLBACK.

How do I move to an absolute row number?

Use syntax like `MOVE ABSOLUTE 500 IN my_cur;` to set the cursor right before the 500th row in the result set.

What happens if I MOVE past the end of the result set?

PostgreSQL moves the cursor to the end and returns a count of 0, indicating no rows were skipped beyond the boundary.