How can I determine in Postgres if a value in one row appears as a value in another row, but in two distinct columns?

edit

You can use a self-join to check if a value in one row appears as a value in another row but in two distinct columns. Here's an example query that demonstrates this:

SELECT t1.column1, t1.column2, t2.column1, t2.column2
FROM mytable t1
INNER JOIN mytable t2 ON t1.column1 = t2.column2 AND t1.column2 = t2.column1
WHERE t1.id <> t2.id;

In this query, we join the table mytable with itself using an inner join. We match rows where t1.column1 equals t2.column2 and t1.column2 equals t2.column1. We also add a condition to exclude rows where t1.id equals t2.id, which prevents matches between the same row.

The result of this query will show you all pairs of rows where the values in two different columns are swapped. For example:

column1 | column2 | column1 | column2
--------+---------+---------+---------
   1    |    2    |    2    |    1
   3    |    6    |    6    |    3

This indicates that the values (1,2) and (2,1) appear in two different rows, as do the values (3,6) and (6,3).