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