PostgreSQL SQL - generating a sequence table against which to do a LEFT OUTER JOIN

Author

generating a sequence table against which to do a LEFT OUTER JOIN

Andrew Hammond

2005-10-27, 8:07 am

So I need an end result that has entries for all days, even when
there's nothing happening on those days, generate from a timestamped
event table. I've already got the interesting entries. Now I need to
fill the holes.

To do this, I'm thinking a LEFT OUTER JOIN against a date sequence
table. So, how do I generate a table with every day from A to B?

Or am I going about this the wrong way?

CREATE TEMP TABLE days ( trans_on DATE );

CREATE OR REPLACE FUNCTION pop_days (DATE, DATE) RETURNS integer AS '
DECLARE
frm_d ALIAS FOR $1;
to_d ALIAS FOR $2;
next_d date;
BEGIN
next_d = frm_d;
LOOP
EXIT WHEN to_d < next_d;
EXECUTE ''INSERT INTO days (trans_on) VALUES ('' || next_d
|| '')'';
SELECT INTO next_d trans_on FROM days ORDER BY trans_on
DESC LIMIT 1;
next_d = next_d + ''1 day''::interval;
END LOOP;
RETURN 1;
END;
' LANGUAGE 'plpgsql';

SELECT pop_days('2005-01-01'::date, '2005-02-01'::date); -- barfs.

David Fetter

2005-10-27, 8:07 am

Andrew Hammond <andrew.george.hammond@gmail.com> wrote:
> So I need an end result that has entries for all days, even when
> there's nothing happening on those days, generate from a timestamped
> event table. I've already got the interesting entries. Now I need to
> fill the holes.
>
> To do this, I'm thinking a LEFT OUTER JOIN against a date sequence
> table. So, how do I generate a table with every day from A to B?
>
> Or am I going about this the wrong way?

What you have is fine, but you're doing extra work. There's this neat
function in PostgreSQL 8.0 or better (you can write one for earlier
versions) called generate_series().

> SELECT pop_days('2005-01-01'::date, '2005-02-01'::date); -- barfs.

SELECT
'2005-01-01'::date + s.i * '1 day'::interval AS "Date",
t.your_date_col
FROM
generate_series(0,'2
005-02-01'::date - '2005-01-01'::date - 1) AS s(i);
LEFT JOIN
your_table t
ON
('2005-01-01'::date + s.i = t.your_date_col);

You can also use generate_series() with a correllated subquery so as
not to have to hard-code dates.

HTH :)

Cheers,
D
--
David Fetter david@fetter.org http://fetter.org/
phone: +1 510 893 6100 mobile: +1 415 235 3778

When a man tells you that he got rich through hard work, ask him:
'Whose?'
Don Marquis, quoted in Edward Anthony, O Rare Don Marquis

Sponsored Links