0

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.

EDIT: Example:

DROP TABLE IF EXISTS tbl;
CREATE TABLE tbl (
    id SERIAL PRIMARY KEY, 
    name VARCHAR, 
    data BYTEA
);

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

DELETE FROM tbl WHERE id='3' or id='5'; 

As you can see the row id is having gaps. I would like to do e.g. something like that (Written in 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.

Thanks

5
  • can you please provide a minimal reproducible example – nbk Sep 30 '20 at 15:53
  • Sorry, sure! Give me a sec. – user2894356 Sep 30 '20 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. – a_horse_with_no_name Sep 30 '20 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 – user2894356 Sep 30 '20 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. – a_horse_with_no_name Sep 30 '20 at 20:05
0

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.