Join tables on columns of composite foreign / primary key in a query

There is a NATURAL JOIN:

SELECT *
FROM   subscription
NATURAL JOIN delivery;

Quoting the manual on SELECT:

NATURAL

NATURAL is shorthand for a USING list that mentions all columns in the two tables that have the same names.

It would work for your test setup, but it's not strictly doing what you ask for. The connection is based on all columns sharing the same name. Foreign keys are not considered. The cases where NATURAL JOIN is a good idea are few and far between.

Simplify code / less verbose

For starters, you could use table aliases and you don't need parentheses around the join conditions with ON (unlike with USING):

SELECT *
FROM   subscription s
JOIN   delivery     d ON d.magazine_id = s.magazine_id
                     AND d.user_id = s.user_id;

Since column names in the join conditions are identical, you can further simplify with USING:

SELECT *
FROM   subscription s
JOIN   delivery     d USING (magazine_id, user_id);

There is no syntax variant making joins based on foreign key constraints automatically. You would have to query the system catalogs and build the SQL dynamically.

Doesn't delivery has two columns representing the foreign key? Then it should work like with a non-composite primary key SELECT * FROM subscription JOIN delivery ON (delivery.magazine_id = subscription.magazine_id AND delivery.user_id = subscription.user_id).