PostgreSQL dynamic table joins

Take a central table Annotation that is linked by one column to different tables. These aren’t foreign keys, since we can’t have a FK to two separate tables on the same column.

An Annotation can apply to a Series or a Vessel. However, a Vessel is part of a Series. If this Annotation is for a Series, we can join straight to it. If it is for a Vessel, we can join to Series via the Vessel.series_id column.

We can represent this with the two columns:

Annotation.target_type: str - What kind of reference this is. For this example, it will either be SERIES or VESSEL.

Annotation.target_id: int - The primary key of the foreign table that this record refers to.

If this annotation is for a Series, target_type='SERIES' and target_id = series.pk. If this annotation is for a Vessel, target_type='VESSEL' and target_id = vessel.pk.

Simple (slow) Join

SELECT 
    a.id,
    v.id,
    s.id
FROM annotation a
LEFT JOIN vessel v ON v.id = a.target_id AND t.target_type = 'VESSEL'
LEFT JOIN series s ON
    CASE
        WHEN a.target_type = 'SERIES' THEN s.id = a.target_id
        WHEN a.target_type = 'VESSEL' THEN s.id = v.series_id
        ELSE NULL::boolean
    END

Time Taken: 43.7s

Using COALESCE

Since we only want to join to Series if Vessel is NULL, we can do

SELECT 
    a.id,
    v.id,
    s.id
FROM annotation a
LEFT JOIN vessel v ON v.id = a.target_id AND t.target_type = 'VESSEL'
LEFT JOIN series s ON COALESCE(v.series_id, a.target_id)

Time Taken: 0.049s