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.
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')
, ('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
.