Untitled

What's a good way to provide versioned rows in PostgreSQL? How to query them?
id BigSerial, PRIMARY KEY
version Integer
text Text
origin BigInt

1,0,"My Text, first Version",null
2,1,"My Text, second Version",1
3,0,"My 2nd Text v1",null
4,1,"My 2nd Text v2",3

create temp table my_table (
  id integer not null,
  version integer not null check(version > 0),
  -- Give a lot of thought to whether text should also be unique. *I* think
  -- it probably should, but it's really application-dependent.
  text Text not null unique,
  primary key (id, version)
);

insert into my_table values
(1, 1, 'My Text, first Version'),
(1, 2, 'My Text, second Version'),
(2, 1, 'My 2nd text v1'),
(2, 2, 'My 2nd text v2')

select id, count(*)
from my_table
group by id;

with current_ver as (
  select id, max(version) as version
  from my_table
  group by id
)
select m.* from my_table m
inner join current_ver c on c.id = m.id and c.version = m.version

select t.id
from table parent
left join table descendants on parent.id = descendants.origin
where descendants.id is null;