Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();


version


PostgreSQL 14.10 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-20), 64-bit



SELECT 1


create table dbKey (
 id serial ,
 name text
);
create table dbData (
 id serial ,
 key_id int ,
 timestamp int 
);

CREATE TABLE



CREATE TABLE


insert into dbKey(name)
values 
('hello'),
('world');
insert into dbData(key_id, timestamp)
values 
(1, 100),
(2, 100),
(1, 101),
(2, 102),
(2, 102);

INSERT 0 2



INSERT 0 5


select * from dbKey;


id
name


1
hello

2
world



SELECT 2


select * from dbData;


id
key_id
timestamp


1
1
100

2
2
100

3
1
101

4
2
102

5
2
102



SELECT 5


-- latest version (tstmp = 102)
SELECT
 dbKey.*, finalDBData.*
FROM
 dbKey,
 (
 SELECT
 *,
 rank() OVER (PARTITION BY key_id ORDER BY TIMESTAMP DESC) AS rank
 FROM
 dbData where "timestamp" <= 102) finalDBData
where rank =1 
and finalDBData.key_id = dbKey.id;


id
name
id
key_id
timestamp
rank


1
hello
3
1
101
1

2
world
5
2
102
1

2
world
4
2
102
1



SELECT 3


-- non-latest version (tstmp = 101)
SELECT
 dbKey.*, finalDBData.*
FROM
 dbKey,
 (
 SELECT
 *,
 rank() OVER (PARTITION BY key_id ORDER BY TIMESTAMP DESC) AS rank
 FROM
 dbData where "timestamp" <= 101) finalDBData
where rank =1 
and finalDBData.key_id = dbKey.id;


id
name
id
key_id
timestamp
rank


1
hello
3
1
101
1

2
world
2
2
100
1



SELECT 2


-- get added row in ver_102 against ver_101
with ver_102 as (
-- latest version (tstmp = 102)
SELECT
 dbKey.*, finalDBData.*
FROM
 dbKey,
 (
 SELECT
 *,
 rank() OVER (PARTITION BY key_id ORDER BY TIMESTAMP DESC) AS rank
 FROM
 dbData where "timestamp" <= 102) finalDBData
where rank =1 
and finalDBData.key_id = dbKey.id
),
ver_101 as (
SELECT
 dbKey.*, finalDBData.*
FROM
 dbKey,
 (
 SELECT
 *,
 rank() OVER (PARTITION BY key_id ORDER BY TIMESTAMP DESC) AS rank
 FROM
 dbData where "timestamp" <= 101) finalDBData
where rank =1 
and finalDBData.key_id = dbKey.id
)
SELECT * FROM ver_102
EXCEPT 
SELECT * FROM ver_101;


id
name
id
key_id
timestamp
rank


2
world
5
2
102
1

2
world
4
2
102
1



SELECT 2


-- get added row in ver_102 against ver_101
with ver_102 as (
-- latest version (tstmp = 102)
SELECT
 dbKey.*, finalDBData.*
FROM
 dbKey,
 (
 SELECT
 *,
 rank() OVER (PARTITION BY key_id ORDER BY TIMESTAMP DESC) AS rank
 FROM
 dbData where "timestamp" <= 102) finalDBData
where rank =1 
and finalDBData.key_id = dbKey.id
),
ver_101 as (
SELECT
 dbKey.*, finalDBData.*
FROM
 dbKey,
 (
 SELECT
 *,
 rank() OVER (PARTITION BY key_id ORDER BY TIMESTAMP DESC) AS rank
 FROM
 dbData where "timestamp" <= 101) finalDBData
where rank =1 
and finalDBData.key_id = dbKey.id
),
diff as (
SELECT * FROM ver_102
EXCEPT 
SELECT * FROM ver_101)
select key_id, count(diff) FROM diff GROUP BY key_id;


key_id
count


2
2



SELECT 1


-- get added row in ver_102 against ver_100
with ver_102 as (
-- latest version (tstmp = 102)
SELECT
 dbKey.*, finalDBData.*
FROM
 dbKey,
 (
 SELECT
 *,
 rank() OVER (PARTITION BY key_id ORDER BY TIMESTAMP DESC) AS rank
 FROM
 dbData where "timestamp" <= 102) finalDBData
where rank =1 
and finalDBData.key_id = dbKey.id
),
ver_100 as (
SELECT
 dbKey.*, finalDBData.*
FROM
 dbKey,
 (
 SELECT
 *,
 rank() OVER (PARTITION BY key_id ORDER BY TIMESTAMP DESC) AS rank
 FROM
 dbData where "timestamp" <= 100) finalDBData
where rank =1 
and finalDBData.key_id = dbKey.id
),
diff as (
SELECT * FROM ver_102
EXCEPT 
SELECT * FROM ver_100)
select key_id, count(diff) FROM diff GROUP BY key_id;


key_id
count


1
1

2
2



SELECT 2