Re: Limitting full join to one match - Mailing list pgsql-general

Hi Ron,

Ron wrote:
> On 12/05/2018 06:34 PM, Phil Endecott wrote:
>> Dear Experts,
>>
>> I have a couple of tables that I want to reconcile, finding rows
>> that match and places where rows are missing from one table or the
>> other:
>>
>> db=> select * from a;
>> +------------+--------+
>> |    date    | amount |
>> +------------+--------+
>> | 2018-01-01 |  10.00 |
>> | 2018-02-01 |   5.00 |  <-- missing from b
>> | 2018-04-01 |   5.00 |
>> +------------+--------+
>>
>> db=> select * from b;
>> +------------+--------+
>> |    date    | amount |
>> +------------+--------+
>> | 2018-01-01 |  10.00 |
>> | 2018-03-01 |   8.00 |  <-- missing from a
>> | 2018-04-01 |   5.00 |
>> +------------+--------+
>>
>> db=> select a.date, a.amount, b.date, b.amount from a full join b using (date,amount);
>> +------------+--------+------------+--------+
>> |    date    | amount |    date    | amount |
>> +------------+--------+------------+--------+
>> | 2018-01-01 |  10.00 | 2018-01-01 |  10.00 |
>> | 2018-02-01 |   5.00 |            |        |
>> |            |        | 2018-03-01 |   8.00 |
>> | 2018-04-01 |   5.00 | 2018-04-01 |   5.00 |
>> +------------+--------+------------+--------+
>>
>> This works fine until I have multiple items with the same date
>> and amount:
>>
>> db=> select * from a;
>> +------------+--------+
>> |    date    | amount |
>> +------------+--------+
>> | 2018-01-01 |  10.00 |
>> | 2018-02-01 |   5.00 |
>> | 2018-04-01 |   5.00 |
>> | 2018-05-01 |  20.00 |  <--
>> | 2018-05-01 |  20.00 |  <--
>> +------------+--------+
>>
>>
>> db=> select * from b;
>> +------------+--------+
>> |    date    | amount |
>> +------------+--------+
>> | 2018-01-01 |  10.00 |
>> | 2018-03-01 |   8.00 |
>> | 2018-04-01 |   5.00 |
>> | 2018-05-01 |  20.00 |  <--
>> | 2018-05-01 |  20.00 |  <--
>> +------------+--------+
>
> What's your PK on "a" and "b"?

These input tables can have duplicate rows, so defining a primary key
requires something like a row ID or similar.


> (Also, gmail seems to think that all -- or at least most -- of your email is 
> spam.)

Yes, it is becoming increasingly difficult to persuade gmail etc. that
you are not a spammer if you run your own mail server.  If you have any
interesting headers suggesting exactly what they disliked about my message,
could you please forward them off-list?  Thanks.


Regards, Phil.