Postgres 11

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

select version();


version


PostgreSQL 11.10 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1 20191121 (Red Hat 8.3.1-5), 64-bit


CREATE TABLE c_entry (
  id BIGSERIAL PRIMARY KEY,
  entry_name text 
);

CREATE TABLE b_entry (
  id BIGSERIAL PRIMARY KEY,
  entry_name text 
);

CREATE TABLE a_entry (
  id BIGSERIAL PRIMARY KEY,
  entry_name text 
);

CREATE TABLE C (
  id BIGSERIAL REFERENCES C_entry(id),
  row_id BIGSERIAL PRIMARY KEY,
  row_name text,
  valid tsrange
);

CREATE TABLE B (
  id BIGSERIAL REFERENCES B_entry(id),
  row_id BIGSERIAL PRIMARY KEY,
  row_name text,
  valid tsrange,
  c_lookup BIGSERIAL REFERENCES C_entry(id)
);

CREATE TABLE A (
  id BIGSERIAL REFERENCES A_entry(id),
  row_id BIGSERIAL PRIMARY KEY,
  row_name text,
  valid tsrange,
  b_lookup BIGSERIAL REFERENCES B_entry(id), 
  c_lookup BIGSERIAL REFERENCES C_entry(id)
);
INSERT INTO C_entry (id, entry_name) VALUES
(1, 'cinit'),
(2, 'cinot'),
(3, 'cinat');
INSERT INTO B_entry (id, entry_name) VALUES
(1, 'binit');
INSERT INTO A_entry (id, entry_name) VALUES
(1, 'Ainit');





INSERT INTO C (id, row_name, valid) VALUES
(1, 'cinit', tsrange('1990-01-01 00:00:00','2100-01-01 00:00:00')),
(2, 'cinot', tsrange('2100-01-01 00:00:00','infinity')),
(3, 'cinat', tsrange('2000-01-01 00:00:00','infinity'))
returning *;


INSERT INTO B (id, row_name, valid, c_lookup) VALUES
(1, 'binit', tsrange('1990-01-01 00:00:00','2100-01-01 00:00:00'), 1),
(1, 'binit', tsrange('2100-01-01 00:00:00','3000-01-01 00:00:00'), 2)
returning *;


INSERT INTO A (id, row_name, valid, b_lookup, c_lookup) VALUES
(1, 'Ainit', tsrange('1990-01-01 00:00:00','2000-01-01 00:00:00'), 1,1),
(1, 'Ainit', tsrange('2000-01-01 00:00:00','3000-01-01 00:00:00'), 1,3)
returning *;
3 rows affected

1 rows affected

1 rows affected



id
row_id
row_name
valid


1
1
cinit
["1990-01-01 00:00:00","2100-01-01 00:00:00")

2
2
cinot
["2100-01-01 00:00:00",infinity)

3
3
cinat
["2000-01-01 00:00:00",infinity)




id
row_id
row_name
valid
c_lookup


1
1
binit
["1990-01-01 00:00:00","2100-01-01 00:00:00")
1

1
2
binit
["2100-01-01 00:00:00","3000-01-01 00:00:00")
2




id
row_id
row_name
valid
b_lookup
c_lookup


1
1
Ainit
["1990-01-01 00:00:00","2000-01-01 00:00:00")
1
1

1
2
Ainit
["2000-01-01 00:00:00","3000-01-01 00:00:00")
1
3

id	row_id	row_name	valid
1	1	cinit	["1990-01-01 00:00:00","2100-01-01 00:00:00")
2	2	cinot	["2100-01-01 00:00:00",infinity)
3	3	cinat	["2000-01-01 00:00:00",infinity)

id	row_id	row_name	valid	c_lookup
1	1	binit	["1990-01-01 00:00:00","2100-01-01 00:00:00")	1
1	2	binit	["2100-01-01 00:00:00","3000-01-01 00:00:00")	2

id	row_id	row_name	valid	b_lookup	c_lookup
1	1	Ainit	["1990-01-01 00:00:00","2000-01-01 00:00:00")	1	1
1	2	Ainit	["2000-01-01 00:00:00","3000-01-01 00:00:00")	1	3