postgresqlpostgresql-11

Fetch limit in Postgres cursor

Is there an option in Postgres inline with the Oracle LIMIT clause while we do fetch on cursors? I'm using a cursor which might hold several millions of rows which would slow down the cursor performance. Do we have any option to limit the number of rows for each fetch and complete the fetch in multiple batches?

Solution

  • Yes, that is what a cursor does by default:

    BEGIN;  -- start a transaction
PREPARE c CURSOR FOR SELECT ...
FETCH 1000 FROM c;
FETCH 1000 FROM c;
...
COMMIT;

    The cursor doesn't “hold” the data, it fetches them from the table as you run FETCH.