title: PostgreSQL recursive parent/child query
body: <p>I'm having some trouble working out the PostgreSQL documentation for recursive queries, and wonder if anyone might be able to offer a suggestion for the following.</p>
  
  <p>Here's the data:</p>
  
  <pre><code>                                            Table "public.subjects"
        Column       |            Type             | Collation | Nullable |               Default                
  -------------------+-----------------------------+-----------+----------+--------------------------------------
   id                | bigint                      |           | not null | nextval('subjects_id_seq'::regclass)
   name              | character varying           |           |          | 
  
  
  
                                          Table "public.subject_associations"
     Column   |            Type             | Collation | Nullable |                     Default                      
  ------------+-----------------------------+-----------+----------+--------------------------------------------------
   id         | bigint                      |           | not null | nextval('subject_associations_id_seq'::regclass)
   parent_id  | integer                     |           |          | 
   child_id   | integer                     |           |          | 
  </code></pre>
  
  <p>Here, a "subject" may have many parents and many children. Of course, at the top level a subject has no parents and at the bottom no children. For example:</p>
  
  <pre><code> parent_id  |  child_id  
  ------------+------------
       2      |     3
       1      |     4
       1      |     3
       4      |     8
       4      |     5
       5      |     6
       6      |     7
  </code></pre>
  
  <p>What I'm looking for is starting with a child_id to get all the ancestors, and with a parent_id, all the descendants. Therefore:</p>
  
  <pre><code>parent_id 1 -&gt; children 3, 4, 5, 6, 7, 8
  parent_id 2 -&gt; children 3
  
  child_id 3 -&gt; parents 1, 2
  child_id 4 -&gt; parents 1
  child_id 7 -&gt; parents 6, 5, 4, 1
  </code></pre>
  
  <p>Though there seem to be a lot of examples of similar things about I'm having trouble making sense of them, so any suggestions I can try out would be welcome. </p>