Today I Learned

hashrocket A Hashrocket project

Postgres Natural joins squash unneeded columns

Dealing mostly with Rails style databases we generally write inner joins on ids and foreign keys but there are other ways to create joins, the most fun being the natural join.

The below query works like a natural join but uses inner join syntax. When two tables having columns with the same name, join on that column. You can see that both columns have been returned even though they have the same value.

chris=# select * from (select 1 x) foo inner join (select 1 x) bar on bar.x = foo.x ;
 x | x
---+---
 1 | 1
(1 row)

When using the natural join syntax the query is less verbose and only returns one column. Postgres knows that they have the same value, thats the whole point! So only present one column.

chris=# select * from (select 1 x) foo natural join (select 1 x) bar;
 x
---
 1
(1 row)
See More #sql TILs
Looking for help? Hashrocket developers believe that data quality is as important as code quality. We enjoy all the challenges of relational databases, from finding the fastest index, to structuring data to fit the needs of an application. We're eager to share our experiences; check out PG Casts, our series of free PostgreSQL screencasts.