CODEWITHSUNDEEP
sqlpostgresqlappendtime-series
eternity1
eternity1

Reputation: 681

Postgresql: append two tables with different columns

I would like to append one table to the other; both tables may have different columns. The result should be a table with all columns and where values do not exist, it should be a missing observation. The data are time series - which I am getting from different sources due to time span constraints - so I need to "stack" them on each other, but it could be that one or the other column is added or dropped off.

As there is a little overlap in the rows I am looking for a solution that would take the data of first table. The problem is then for those column not existing in table 1, they wouldn't exist either when I pick table 1 over table 2.

Current solution is to cut-off table 2 so there is no overlap.

table 1:

date        AA    BB    CC    DD
20100101     9    10    11    12
20100102    10    11    12    13

table 2:

date        AA    BB    CC    EE    FF
20100102                99    99    10
20100103    11    12    13    14    10
20100104    12    13    14    15    11

and the result should be

date        AA    BB    CC    DD    EE    FF
20100101     9    10    11    12    
20100102    10    11    12    13    99    10
20100103    11    12    13          14    10
20100104    12    13    14          15    11

So I do not in fact have anything to "join" on as suggested here: SQL union of two tables with different columns

Upvotes: 1

Views: 3956

Answers (1)

Barbaros Özhan
Barbaros Özhan

Reputation: 65323

coalesce function may be used like in the following :

select coalesce(t1.date,t2.date) date, 
       coalesce(t1.aa,t2.aa) aa, 
       coalesce(t1.bb,t2.bb) bb,        
       coalesce(t1.cc,t2.cc) cc, 
       t1.dd,
       t2.ee, 
       t2.ff       
  from table1 t1 full outer join table2 t2 on ( t1.date = t2.date );

SQL Fiddle Demo

Upvotes: 5

Related Questions