Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table data(
  row int,
  key varchar(50),
  value varchar(50),
  version int
);

create table data_version(
  row int,
  next_version int
);

-------------- step 1
-- insert initial data
insert into data (row, key, value, version) values
  (1, 'key 1', 'value 1', 1), 
  (2, 'key 2', 'value 2', 1),
  (3, 'key 3', 'value 3', 1)
;
-- no need to update data_version table
-- as all data is the latest version

-------------- step 2
-- now an update to some entries at v2
insert into data (row, key, value, version) values
  (4, 'key 1', 'value 1 updated at v2', 2), -- updates at v2
  (5, 'key 2', 'value 2 updated at v2', 2)
;

-- update the data_version table to indicate there's a new version of some values
insert into data_version (row, next_version) values
  (1, 2), -- next version fo row 1 is version 2
  (2, 2) -- next version fo row 2 is version 2
;

-------------- step 3
insert into data (row, key, value, version) values
  (6, 'key 3', 'value 3 updated at v3', 3)
;

insert into data_version (row, next_version) values
  (3, 3) -- next version for row 3 is version 3
;

-------------- step 4
insert into data (row, key, value, version) values
  (7, 'key 1', 'value 1 updated at v4', 4)
;
insert into data_version (row, next_version) values
  (4, 4) -- next version for row 4 is version 3
;

-- Change the version number below (in both places) to see the data for each
-- key and value at each version
select d.key, d.value from data d
	left join data_version dv on dv.row = d.row
    where d.version <= 2 and (dv.next_version > 2 or dv.next_version is null)
    order by key asc -- order by is just to make it easier to reason about in the output
  ;

CREATE TABLE

CREATE TABLE

INSERT 0 3

INSERT 0 2

INSERT 0 2

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

key	value
key 1	value 1 updated at v2
key 2	value 2 updated at v2
key 3	value 3

SELECT 3