Create result set with all records from LEFT table, and from the RIGHT table only bring records that do no exist in left already

Question

I'm having a bit of trouble wrapping my head around a way to make this JOIN work.

Postgres 11.5

I have two tables:

Table1:
customer_id
date
(various other fields)

Table2:
customer_id
date
(various other fields)

The tables are not the same, but the customer_id's and dates are in the same format so will be able to be joined.

I am trying to create a result set that contains both of the tables combined together (all columns) but with the following logic applied:

All data from Table1
Only records from Table 2 that DO NOT exist in Table 1
Joined on customer_id and date

My end goal is that I am trying to create a stacked area chart with this result set, but there are situations where a customer could be both in Table 1 and Table 2, so for a single given day, if someone exists who is like that, I don't want to double count that individual.

Appreciate the help!

Answer 1

Assuming all the columns are compatible:

SELECT * from table1
UNION ALL
SELECT table2.* from table2 left join table1 using (customer_id, date)
    where table1.customer_id is null

If not all the columns are compatible, you will have to do a bit of tedious manipulation of the select lists, rather than using *.