get n records at a time from a temporary table
- by Claudiu
I have a temporary table with about 1 million entries. The temporary table stores the result of a larger query. I want to process these records 1000 at a time, for example. What's the best way to set up queries such that I get the first 1000 rows, then the next 1000, etc.? They are not inherently ordered, but the temporary table just has one column with an ID, so I can order it if necessary. I was thinking of creating an extra column with the temporary table to number all the rows, something like:
CREATE TEMP TABLE tmptmp AS
SELECT ##autonumber somehow##, id
FROM .... --complicated query
then I can do:
SELECT * FROM tmptmp WHERE autonumber>=0 AND autonumber < 1000
etc... how would I actually accomplish this? Or is there a better way? I'm using Python and PostgreSQL.
