Postgresql – Versioning entities in db with draft option, select query

postgresqlversion control

I have postgresql db where i keep data for jee application. User can edit those data without creating new version of entity(draft). During edit, entity is copied as draft(version set to -1) after editing user can save all changes as new version. My problem is to create proper sql query to get data by version instead of extract all of them and do it in application.

Table schema:

id(uniq), field_id,version,otherFields....

id is uniq value and automatically incremented. When user start editing new field is created with field_id=parent.id. So i can extract all versions of field using group by field_id

Case 1

I want to get current version which means max value of version field or version set to -1.

Case 2

I want to get specific version. It is not that easy because data in db are not changed if field was not edited(to save space). For example field a was created with version 1 and then changes was done on other fields. Current global version is 7 but field a still have one entry in db with version=1(this field was not edited). So to extract specific version different than current i need to get MAX(version) where version<=:selected_version

Example data:

id(uniq),field_id,version
1,1,1
3,1,7
55,1,-1
53,53,14

Entity with field_id=1 has 2 version and draft(user is editing it).

Entity with field_id=53 has one version and no draft.

I hope that my explanation of problem is understandable. Could you help me with those sql query? It could be two queries, one for actual version with draft and other for old versions.

Case 2 probably solution

I think that specific version i can extract using such query

select field_id, max(version) ver from field_mappings where version<4 and version!='-1' group by field_id

I don't know why i can't extract id. When i change query to

select *, max(version) from field_mappings where version<4 and version!='-1' group by field_id

Then i got error

ERROR: column "field_mappings.id" must appear in the GROUP BY clause or be used in an aggregate function
SQL: 42803

And still don't know how to add case for draft(max(version) or version=-1).

Best Answer

You can use the following, which relies on DISTINCT ON, and turns calculates version -1 as 9999999:

select DISTINCT ON (field_id) *
from field_mappings 
where COALESCE(NULLIF(version, -1), 9999999) < 4
ORDER BY field_id, COALESCE(NULLIF(version, -1), 9999999) DESC

The query retrieves all the rows with unique fields (by field_id), prioritising the version you specify, or the draft one (which has its priority bumped to the top).
If you want the draft or last version, then use < 10000000 instead of < 4.

Related Question