CODEWITHSUNDEEP
sqldatabasepostgresql
Anand Srinivasan
Anand Srinivasan

Reputation: 470

Fill postgres table rows with missing column values

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!

Upvotes: 1

Views: 651

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

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;

Upvotes: 3

Related Questions