My data table has the following columns:

id INTEGER, name TEXT, created TIMESTAMP, deleted TIMESTAMP

I want to product a report of the count of each name (which can appear several times in the table) that is active each year. (also, the deleted timestamp may be null, if it's currently still active).

So far I’ve managed to do this by manually entering the years in a long list of union statements (see below). I’m sure there’s a better way! And I have many more similar queries to perform. I tried to create a PL/pgSQL function, but couldn’t figure out how to get the year as a variable and also how to get the correct output. I would be very happy with a statement, or PL/pgSQL function to achieve this.

((select '2016' yr, name, count(*) from data
where (((deleted - '2016-01-01'::timestamp) > '0 secs') or (deleted is null))
and (created - '2016-01-01'::timestamp) <= '0 secs'
group by name
order by count desc)
union all
((select '2015' yr, name, count(*) from data
where (((deleted - '2015-01-01'::timestamp) > '0 secs') or (deleted is null))
and (created - '2015-01-01'::timestamp) <= '0 secs'
group by name
order by count desc)

I got the years using:

select distinct date_part('year',created) from data order by date_part('year',created);

And then manually entered them in the very long union statement. (In my case 2007-2016!)

migrated from gis.stackexchange.com Apr 5 '16 at 12:15

This question came from our site for cartographers, geographers and GIS professionals.

  • PL/SQL is the procedural language of Oracle. I assume you mean PL/pgSQL? And always provide your version of Postgres, please. – Erwin Brandstetter Apr 7 '16 at 0:08

With generate_series() in a LATERAL join (Postgres 9.3+) and date_trunc(), this can be short & simple:

SELECT EXTRACT(YEAR FROM yr)::text AS year, name, count(*) AS ct
FROM   data
     , generate_series(date_trunc('year', created)  -- LATERAL join
                     , COALESCE(deleted, localtimestamp)
                     , interval '1 year') yr
GROUP  BY yr, name
ORDER  BY yr, ct DESC;

That's all. Returns results for all years between the earliest created and the current year.
The trick is to generate one row for each year the base row overlaps with, before aggregating.


SELECT EXTRACT(YEAR FROM yr) AS year, d.name, count(*) AS count
FROM   generate_series ((SELECT date_trunc('year', min(created)) FROM data)
                       , localtimestamp, interval '1 year') yr
JOIN   data d ON d.created < yr::timestamp + interval '1 year'
            AND (d.deleted > yr::timestamp OR d.deleted IS NULL)
GROUP  BY yr, d.name
ORDER  BY count(*) DESC;

This generates the whole range of years before joining. May be more convenient to compute numbers for a manual selection of years.


If you need to optimize performance for a small set of years out of a much bigger table, a GiST index on a tsrange type would be an option:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.