title: Combine two tables into a new one so that select rows from the other one are ignored
body: <p>I have two tables that have identical columns. I would like to join these two tables together into a third one that contains all the rows from the first one and from the second one all the rows that have a date that doesn't exist in the first table for the same location.</p>
  
  <p>Example:</p>
  
  <p>transactions:</p>
  
  <pre><code>date    |location_code| product_code | quantity 
  ------------+------------------+--------------+----------
  2013-01-20 | ABC         | 123          |  -20         
  2013-01-23 | ABC         | 123          |  -13.158
  2013-02-04 | BCD         | 234          |  -4.063
  </code></pre>
  
  <p>transactions2:</p>
  
  <pre><code>date    |location_code| product_code | quantity 
  ------------+------------------+--------------+----------
   2013-01-20 | BDE         | 123          |  -30         
   2013-01-23 | DCF         | 123          |  -2
   2013-02-05 | UXJ         | 234          |  -6
  </code></pre>
  
  <p>Desired result:</p>
  
  <pre><code>date    |location_code| product_code | quantity 
  ------------+------------------+--------------+----------
   2013-01-20 | ABC         | 123          |  -20         
   2013-01-23 | ABC         | 123          |  -13.158
   2013-01-23 | DCF         | 123          |  -2
   2013-02-04 | BCD         | 234          |  -4.063
   2013-02-05 | UXJ         | 234          |  -6
  </code></pre>
  
  <p>How would I go about this? I tried for example this:</p>
  
  <pre><code>SELECT date, location_code, product_code, type, quantity, location_type, updated_at
        ,period_start_date, period_end_date
     INTO transactions_combined
     FROM ( SELECT * FROM transactions_kitchen k
            UNION ALL
            SELECT *
              FROM transactions_admin h
              WHERE h.date NOT IN (SELECT k.date FROM k)
          ) AS t;
  </code></pre>
  
  <p>but that doesn't take into account that I'd like to include the rows that have the same date, but different location. I have Postgresql 9.2 in use.</p>