PostgreSQL Question posted in
The official documentation can be found here.

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

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.)

Tags:

2

Answers


  1. 0

    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 they 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 at 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 joining of tables, but more to be able to ensure that when you join the tables, the values will be valid.

    Login or Signup to reply.
  2. 0

    You can do a natural join(inner join) on Postgresql but left join would require you to specify the join condition.

    SELECT * FROM foo NATURAL JOIN bar;

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search