Display both values of same column after comparing rows (with inner join)

When you perform an inner join to compare 2 values of the same column, but different rows, with eachother. How do you include both compared values of the same column? Without error “column specified more than once”

In the new table I want to see a.var2 and b.var2.

CREATE TABLE newtable AS
SELECT
    a.var1,
    a.var2,
    CASE WHEN a.var2 = b.var2 THEN 'yes' AS equal
FROM
    (
        SELECT * FROM table
        WHERE var3 = x
    ) a
INNER JOIN (
        SELECT * FROM table
        WHERE var3 = y
    ) b ON a.var1 = b.var1

Answer

You need alias names for the two columns:

CREATE TABLE newtable AS
SELECT
  a.var1,
  a.var2 AS a_var2,
  b.var2 AS b_var2,
  a.var2 = b.var2 AS equal
FROM (SELECT * FROM table WHERE var3 = 'x') a
JOIN (SELECT * FROM table WHERE var3 = 'y') b ON a.var1 = b.var1;

PostgreSQL has a boolean type. I am using this in above query and I generally recommend doing so, instead of using a string with ‘yes’/’no’, ‘YES’/’NO’, ‘Y’/’N’ or whatever values you happen to decide for. The boolean data type is the appropriate type here. In this specific case, though, I wouldn’t write it at all, because the information is redundant. Just look at the two columns and you see whether they are equal or not.