PostgreSQL How to get friend requests in table by seeing if there is a duplicate of the reverse of a column? I've a table, 

CREATE TABLE friends (
    friend1 serial references users(id),
    friend2 serial references users(id),
    unique (friend1, friend2)
);


Friends are in no particular order, but I need to have a query that returns friends where it is a one-way friendship. For example, if the data is:

friend1 | friend2
-----------------
1       | 2
2       | 1
3       | 2


the query for friend id=2 should return: 

friend1 | friend2
-----------------
3       | 2


because there is not another row where friend1=2 and friend2=3.
You can use EXCEPT to remove the complementing rows.

SELECT friend1,friend2 FROM friends 
    WHERE friend1=2 or friend2=2
EXCEPT
SELECT friend2,friend1 FROM friends
    WHERE friend1=2 or friend2=2;


But maybe using a CTE will perform better.

WITH buddies 
AS ( SELECT friend1,friend2 FROM friends 
        WHERE friend1=2 or friend2=2 )
SELECT friend1,friend2 FROM buddies
EXCEPT
SELECT friend2,friend1 FROM buddies


Unfolding the or into a union may help performance too.

WITH buddies 
AS ( SELECT friend1,friend2 FROM friends 
        WHERE friend1=2
     UNION ALL
     SELECT friend1,friend2 FROM friends 
        WHERE friend2=2
   )
SELECT friend1,friend2 FROM buddies
EXCEPT
SELECT friend2,friend1 FROM buddies