dajood dajood - 6 months ago 9
SQL Question

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

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

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.