Fill postgres table rows with missing column values

Question

I have a PostgreSQL table like this:

table1:

MARKET        character varying 10
COST1         Number

MARKET       DATE           VALUE
A            01/01/2018      10   
A            01/02/2018      45
A            01/04/2018      12 
B            01/01/2018      12
B            01/05/2018      12
B            01/04/2018      12

I have another table like:

table2:

DATE
01/01/2018 
01/02/2018 
01/03/2018 
01/04/2018 
01/05/2018

i am trying to join these two tables such that each "MARKET" from table1 should have all the date ranges in table2. If the "DATE" is not present in table1 its corresponding "VALUE" column should be zero.

OUTPUT should be:

    MARKET       DATE           VALUE
    A            01/01/2018      10   
    A            01/02/2018      45
    A            01/03/2018      0
    A            01/04/2018      12 
    A            01/05/2018      0
    B            01/01/2018      12
    B            01/02/2018      0
    B            01/03/2018      0
    B            01/04/2018      12
    B            01/05/2018      12

Still pretty new to postgres. Any help will be appreciated!

Please remove the tags for mysql and oracle. Clearly you are not using those products. — JNevill
– JNevill, Commented Jun 8, 2018 at 19:11

Gordon Linoff · Accepted Answer · 2018-06-08 19:11:30Z

3

For this type of problem, generate the rows with a cross join. The use another mechanism (left join) to bring in the values.

select m.market, t2.date, coalesce(t1.value, 0) as value
from (select distinct market from t1) m cross join
     t2 left join
     t1
     on t1.market = m.market and t1.date = t2.date;

answered Jun 8, 2018 at 19:11

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Fill postgres table rows with missing column values

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related