What's a good way to provide versioned rows in PostgreSQL? How to query them?

Question

I want to store different versions of different texts and other data in a table. For the texts, my table looks like this:

id BigSerial, PRIMARY KEY  
version Integer  
text Text  
origin BigInt

Now I want to store different versions of texts in this table like this:

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

I don't know yet how to query for the row with the highest version number for each set of texts.

Answer 1

The Bigserial id number serves no useful purpose.

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')

Number of versions for every id.

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

Current version for every 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

Although I wrote that with a common table expression, you probably want to create a view of current versions. I'd think most applications that access this data will need the current version.

Answer 2

Since not all of the set of texts may get new versions at the same rate, there's no real way of making an assertion on version number that won't have to involve both the id and the origin.

For instance, to know that "5" is the latest version of a particular text set, you'll have to establish that there is no version "6". The way to do that is to see if there is a row with a version "6" that has an origin to the row with version "5". But this just reduces to finding a row that has no other row claiming it as an origin point; you don't need the version number.

So, you can rephrase the question of "do I have the highest version number" as "is there no other row that has my id as its origin". If there is no other row, then this is your newest row, and you can return your result. You can accomplish this with something like the following query:

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

The only rows in the resulting joined table where descendant.id will be null are the ones that are the newest. Notice that both parent and descendants are aliased from the same table. This is what is known as a "self-join", and is handy to do when you have hierarchical data (like your versioning mechanism) stored within a single table.

It is worth noting, though, that this only finds the newest version for you. If you want to know which version this is, then you will definitely benefit from having your version column. Otherwise, you will end up having to do a recursive query, since you won't know the depth of your version list up front. No one likes writing those.

Hope this helps.