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

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)

Collectives™ on Stack Overflow

Comparing two columns in postgres database

2 Answers 2

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Related