Comparing two columns in postgres database

Question

I am somewhat stumped on a seemingly simple problem. I have a table set up such as,

CREATE TABLE cities (
     column_1 TEXT,
     column_2 TEXT);

Where the contents of these tables look like

column_1  |  column_2
---------------------
Atlanta   |  Atlanta
Boston    |  Chicago
Chicago   |  Los Angeles
Seattle   |  Tacoma
NULL      |  Seattle

What query could I run that would look at both of these columns, and despite their order, return where the two columns matchup?

The result I am looking for would be:

column_1  |  column_2
---------------------
Atlanta   |  Atlanta
Chicago   |  Chicago
Seattle   |  Seattle

I have tried:

SELECT *
FROM cities
WHERE column_1 = column_2;

But this only returns the EXACT matches:

column_1  |  column_2
---------------------
Atlanta   |  Atlanta

404 · Accepted Answer · 2018-11-07 14:57:00Z

10

You just need a self join:

SELECT c1.column_1, c2.column_2
FROM cities c1
JOIN cities c2
    ON c1.column_1 = c2.column_2

answered Nov 7, 2018 at 14:57

404

8,7522 gold badges33 silver badges53 bronze badges

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

1 Comment

Ben_S Over a year ago

Thanks! I'll upvote this as the answer when it lets me (it's giving me an 8 minute timeout).

Nick Ellis · Accepted Answer · 2018-11-07 14:58:39Z

1

your query is asking the question where is column_1 == column_2 in the same row. If you want to retrieve all instances of cities existing in both columns you would need to do a self join like so.

SELECT t1.column_1, t2.column_2
FROM cities t1 join cities t2 on (t1.column_1 = t2.column_2)

answered Nov 7, 2018 at 14:58

Nick Ellis

1,07711 silver badges25 bronze badges

Collectives™ on Stack Overflow

Comparing two columns in postgres database

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related