Re: Function with limit and offset - PostgreSQL 9.3

John R Pierce <pierce@xxxxxxxxxxxx> · Thu, 8 Jun 2017 21:56:05 -0700



    On 6/8/2017 6:36 PM, marcinha rocha
      wrote:

    
            UPDATE tablea a SET migrated = yes WHERE a.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