Postgres get rows with one same column value

Question

In Postgres I have a table as below:

Table phones

 |id | phone        |
 | 1 | +12345678    |
 | 2 | +95687445    |
 | 3 | +78945646    |
 | 4 | +12345678    |

As you can see id 1 and 4 have the same phone number.

I want to get all rows which has same phone number but different id like this:

 | id1 | id2 | phone     |
 | 1   | 4   | +12345678 |

I have tried to use JOIN with itself but that returns duplicate results:

select
  p1.id as id1,
  p2.id as id2,
  p1.phone
from phones as p1
  join phones as p2
    on p1.phone = p2.phone

 | id1 | id2 | phone     |
 | 1   | 4   | +12345678 |
 | 4   | 1   | +12345678 |

Gordon Linoff · Accepted Answer · 2020-01-13 13:51:38Z

5

I would recommend array_agg():

select phone, array_agg(id)
from phones
group by phone;

This handles more than two results.

If you specifically want pairs, use join with a filtering condition:

select p1.id as id1, p2.id as id2, p1.phone
from phones p1 join
     phones as p2
     on p1.phone = p2.phone and
        p1.id < p2.id;

answered Jan 13, 2020 at 13:51

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Nicola Lepetit · Accepted Answer · 2020-01-13 13:55:40Z

2

You should add the not equal condition for the IDs:

select
  p1.id as id1,
  p2.id as id2,
  p1.phone
from phones as p1
  join phones as p2
    on p1.phone = p2.phone
where p1.id < p2.id

You'll have duplications in case of more than 2 identical values... For the complete solution, the query is much more complicate.

answered Jan 13, 2020 at 13:53

Nicola Lepetit

83013 silver badges28 bronze badges

I'd try where p1.id < p2.id. To avoid switched duplicates.

Yes, even better, fixed the answer