Re: Function with limit and offset - PostgreSQL 9.3

On 6/8/2017 6:36 PM, marcinha rocha wrote:
> |UPDATEtablea a SETmigrated =yes WHEREa.id =row.id;|
> On my original select, the row will have migrated = false. Maybe All I
> need to put is a limit 2000 and the query will do the rest?

SELECT does not return data in any determinate order unless you use an
ORDER BY.... so LIMIT 2000 would return some 2000 elements, not
neccessarily the 'first' 2000 elements unless you somehow order them by
however you feel 'first' is defined.

WITH ids AS (INSERT INTO tableb (id) SELECT id FROM tablea WHERE
migrated=FALSE ORDER BY id LIMIT 2000 RETURNING id)
UPDATE tablea a SET a.migrated=TRUE WHERE a.id = ids.id
RETURNING COUNT(a.id);

I'm not 100% sure you can do UPDATE .... RETURNING COUNT(...), worse
case the UPDATE RETURNING would be a subquery of a SELECT COUNT()...

--
john r pierce, recycling bits in santa cruz