Can Postgres use the foreign key (between two tables) as the "ON" clause when LEFT JOINing them?

Question

With a schema:

CREATE TABLE foo (
    id SERIAL UNIQUE,
    name VARCHAR(15) NOT NULL,
    domain VARCHAR(255),
);

CREATE TABLE bar (
    foo_id INT UNIQUE NOT NULL REFERENCES foo(id),
    passphrase VARCHAR
);

I need to use:

SELECT * FROM foo LEFT JOIN bar ON bar.foo_id = foo.id;

Can Postgres use the foreign key (between two tables) as a default "ON" clause when LEFT JOINing them?

i.e. I've already put in all the effort to logically link a couple of dozen tables with foreign keys, why do I need to repeat these in every query? (Inferring the ON clauses from the foreign keys appears much safer than making the querier state them.)

Answer 1

There is no such feature like this in Postgresql. SQL in general requires explicitly specifying what you want that query to do, and expecting implicit joins like this would likely cause more problems than it would solve.

For example, you could have multiple foreign keys between the tables. Then it would have to either stop all your previous queries from working when you add the second one or guess which one to use, which are just two bad situations!

It's also not even certain that you would always want to use a foreign key to join the tables.

The point of referential integrity is not to automate the joining of tables, but more to be able to ensure that when you join the tables, the values will be valid.