versioning

my.sql

create table foo_version (
  id VARCHAR(32),
  v BIGINT,
  data TEXT,
  
  PRIMARY KEY (id, v)
);

create table foo (
  id VARCHAR(32),
  v BIGINT,
  PRIMARY KEY (id),
  FOREIGN KEY (id, v) REFERENCES foo_version (id, v) ON DELETE CASCADE
);

insert into foo_version(id, v, data) values
  ('1', 1, 'hello'),
  ('1', 2, 'hello world');
  
insert into foo(id, v) values
  ('1', 2);
  
SELECT foo_version.* FROM foo_version fv JOIN foo ON fv.id=foo.id AND fv.v=foo.v

pg.sql

create table foo(
  id TEXT,
  v BIGINT,
  active BOOLEAN default FALSE,
  data TEXT,
  
  PRIMARY KEY (id, v)
);
create unique index unique_v on foo(id, active) WHERE active;

insert into foo(id, v, active, data) values
 ('1', 1, false, 'hello'),
 ('1', 2, true, 'hello world');
 
BEGIN;
update foo set active=false where id='1' and active;
insert into foo(id, v, active, data) values('1', 3, true, 'hell');
COMMIT;