Left Join Yielding Inner Join Results - PostgreSQL

Question

I'm looking to take values from an event log that occur on a sporadic basis and extend these values on all dates between events. For example:

A full series may look like...

But the event log looks like...

2013-01-01 | value_1
2013-01-04 | value_2

The desired end result is as follows...

2013-01-01 | value 1
2013-01-02 | value 1
2013-01-03 | value 1
2013-01-04 | value 2
2013-01-05 | value 2

As solution I'm trying to generate a full series of dates, left join the selective event log dates onto that full series, and then fill in the NULL values with the desired values, but I'm running into an issue where my left join produces inner join results.

select 
days.date, 
e.value
from
--Generate table for dates
    (select  
    row_number() over (), 
    generate_series::date as date
    from
        generate_series('2009-01-01',current_date + interval '100 days',interval '1 day')) days
--Combine actual change log 
left join
    (select 
    value,
    event_date 
    from event_table 
    where type = 'desired_type') e
        on days.date = e.event_date

My result set is not 1669 rows with full dates but only a few e.value, but instead just a row for each non-NULL days.date, e.value combination. Visually, instead of...

2009-01-01 | value 1
2009-01-02 | NULL
2009-01-03 | NULL
2009-01-04 | value 2
2009-01-05 | NULL
...

...I'm getting...

2009-01-01 | value 1
2009-01-04 | value 2
...

I'd expect this with an inner join, not a left join from days to e.

Is that all your query? Don't you have a `where` clause? – Clodoaldo Neto Apr 18 '13 at 23:27 — Clodoaldo Neto, Apr 18 '13 at 23:27

score 1 · Answer 1 · edited May 23 '17 at 12:12

1

The LEFT JOIN should just work.

You can largely simplify the whole query:

SELECT d.day, e.value
FROM  (
   SELECT rn, '2009-01-01'::date + rn AS day
   FROM   generate_series(0, (now()::date - '2009-01-01'::date) + 100) AS g(rn)
   ) d
LEFT JOIN event_table e ON e.event_date = d.day AND e.type = 'desired_type'

However, only a few days ago somebody asked a very similar question. It has a number of very good answers (including mine I may add). You'll find complete solution for your problem there:
How do I write a join with this unusual matching criteria?

edited May 23 '17 at 12:12

Community

1
1

answered Apr 18 '13 at 23:43

Erwin Brandstetter

605,456
145
1,078
1,228

I will likely use the lead() methodology proposed in the other thread, but am still perplexed as to why "The LEFT JOIN should just work." – Nick Apr 19 '13 at 16:46
1

@Nick Because your query as posted should give the expected result. Sure you are hiding a `where` clause that turns it into an `inner join`. – Clodoaldo Neto Apr 19 '13 at 17:47
My desired end query will have contents in the where clause (I'd like to give results for only specific accounts), however, I haven't build those in yet and am simply evaluating over the generated series and an event log table. – Nick Apr 19 '13 at 22:03
@Nick: If your `LEFT JOIN` is still failing you need to provide a complete test case to reproduce the effect. In theory that's impossible. It would have to be something that is not in your question. Chances are, you will find it when trying to build the test case (and failing, inevitably). – Erwin Brandstetter Apr 20 '13 at 11:41

Left Join Yielding Inner Join Results - PostgreSQL

1 Answers1