Return to Question

Here is a real life example using the "type" solution :

SELECT *
  FROM BinDescription b
  JOIN RobotDescription r
    ON b.physicalContainerType = 'ROBOT'
   AND b.physicalContainerFK = r.robotPK
  JOIN RobotMissionDescription rm
    ON rm.robotFK = r.robotPK
;

Which gives me this plan :

 Merge Join  (cost=225.12..395.31 rows=12026 width=301) (actual time=4.372..67.206 rows=47293 loops=1)
   Merge Cond: (b.physicalcontainerfk = r.robotpk)
   ->  Nested Loop  (cost=222.91..15184.24 rows=88 width=152) (actual time=4.321..44.448 rows=47293 loops=1)
         ->  Index Scan using bindescription_physicalcontainertype_physicalcontainerfk_key on bindescription b  (cost=0.29..13.66 rows=4 width=76) (actual time=0.011..0.017 rows=4 loops=1)
               Index Cond: (physicalcontainertype = 'ROBOT'::physicalcontainertype)
         ->  Bitmap Heap Scan on robotmissiondescription rm  (cost=222.62..3672.39 rows=12026 width=76) (actual time=2.431..7.985 rows=11823 loops=4)
               Recheck Cond: (robotfk = b.physicalcontainerfk)
               Heap Blocks: exact=14246
               ->  Bitmap Index Scan on idx_robotmissiondescription_robotfk  (cost=0.00..219.62 rows=12026 width=0) (actual time=1.629..1.629 rows=11823 loops=4)
                     Index Cond: (robotfk = b.physicalcontainerfk)
   ->  Sort  (cost=2.20..2.29 rows=34 width=149) (actual time=0.044..4.752 rows=47321 loops=1)
         Sort Key: r.robotpk
         Sort Method: quicksort  Memory: 34kB
         ->  Seq Scan on robotdescription r  (cost=0.00..1.34 rows=34 width=149) (actual time=0.007..0.014 rows=34 loops=1)
 Planning time: 1.559 ms
 Execution time: 69.480 ms

• Having a column type and a column FK so I can make joins like type = 'A' AND a.pk = c.a_fk (this is what we use right now)
• Using inheritance : Both A and B inherit a sequence from a parent table and C has a foreign key to the parent table, but this is not possible with postgresql
• Using the same principle as the inheritance but with another table D. Both A and B have a reference to D, C also has a reference to D, and I can do a join like FROM c JOIN a ON a.d_fk = c.d_fk
• Using a column by table I want to have a foreign key to

A is filled with 20 rows, B with 10000.
C is also filled with 10000, both a_fk and b_fk cannot be filled at the same time. All rows are filled with b_fk (which must be unique) except for 5 rows where a_fk is filled (which must be unique too).

I tried on both postgres 9.6 and 11, I would prefer a solution for 9.6 because this is what we use but I would accept a solution for 11.

• Having a column type and a column FK so I can make joins like type = 'A' AND a.pk = c.a_fk (this is what we use right now)
• Using inheritance : Both A and B inherit a sequence from a "parent" table and C has a foreign key to the "parent" table, but this is not possible with PostgreSQL
• Using the same principle as the inheritance but with another table D. Both A and B have a reference to D, C also has a reference to D, and I can do a join like FROM c JOIN a ON a.d_fk = c.d_fk
• Using a column by table I want to have a foreign key to

• A is filled with 20 rows, B with 10000.
• C is also filled with 10000, both a_fk and b_fk cannot be filled at the same time. All rows are filled with b_fk (which must be unique) except for 5 rows where a_fk is filled (which must be unique too).

I tried on both Postgres 9.6 and 11, I would prefer a solution for 9.6 because this is what we use but I would accept a solution for 11.