Re: Versioning Schema SQL ideas needed

On Mon, Jan 26, 2015 at 2:38 PM, Tim Smith <randomdev4+postgres(at)gmail(dot)com>
wrote:

> create table templates(
> template_id int not null primary key,
> template_groupid int not null,
> template_version int not null
> template_text text not null);
>
> Would I need to resort to using a CTE or subquery to make this sort of
> thing work ? I can't seem to make it work with group by since group
> by expects aggregation. Surely I don't need to normalise it into a
> couple of tables ?
>

What sort of thing? Selecting the latest version of each template? Try

SELECT
DISTINCT ON (template_group_id) template_id, template_group_id,
template_version, template_text
FROM
templates
ORDER BY
template_group_id, template_version DESC

You could even create a latest_templates view for this. DISTINCT ON is my
favorite lesser-known Postgres feature.