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
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
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.