I got a Postgres 13 DB and a table with an id based on a SERIAL. After deleting rows such a sequence would have gaps.

In each row binary data is stored too and I would like to process it row after row moving backward or forward.

My idea would be to do SELECT id FROM tbl; and then use this as a lookup table. Another idea would be to create a view with a new continuous sequence and to get the max value and then iterate between sequence start and max value.


  name VARCHAR, 
  data BYTEA

INSERT INTO tbl (name, data) VALUES
  ('a', '0xff')
, ('b', '0xff')
, ('v', '0xff')
, ('w', '0xff')
, ('t', '0xff')
, ('u', '0xff')

DELETE FROM tbl WHERE id IN (3, 5);

As you can see the row id is having gaps. I would like to do e.g. something like this (pseudocode):

std::vector<int> vec = SELECT id FROM tbl; -- -> Store values in C++ vector named vec

for(int i=0; i<vec.size(); i++) { -- Loop through vector resp. every row in the database table
    SELECT * FROM tbl WHERE id=vec[i] -- Query every row in tbl
    //Do something with the binary data -- Process its data

Is there a better way to achieve this?

If it plays a role, I am using libpq.

  • can you please provide a minimal reproducible example
    – nbk
    Sep 30, 2020 at 15:53
  • Sorry, sure! Give me a sec. Sep 30, 2020 at 16:02
  • Gaps in generated values are not a problem. The only job of a generated primary key is to be unique. It is completely irrelevant whether that value is 1,42,-534 or 5438947. There is nothing you need to do. Just move on. Sep 30, 2020 at 16:29
  • Right, I can just get the row count and move on based on the row id and if the result is zero I move forward. Thanks Sep 30, 2020 at 16:46
  • No need to get a row count "and move forward". Just insert a new row and let the sequence generate a new value. Sep 30, 2020 at 20:05

1 Answer 1


Seeing the loop in pseudo-code, it seems like you want a SQL cursor. it's instantiated with DECLARE associated to a query with an ORDER BY if needed, and you can use MOVE to move forward or backward, and FETCH to fetch the current row, or several rows, and finally CLOSE when you're done.

All these are SQL statements and independent from any client-side language.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.