title: Postgres: Using WITH RECURSIVE to build list of item and their parents
body: <p>I have a schema that looks like this:</p>
  
  <pre><code>CREATE TABLE category (
    id SERIAL PRIMARY KEY,
    parent INTEGER REFERENCES category(id) DEFERRABLE,
    name TEXT NOT NULL UNIQUE );
  SET CONSTRAINTS ALL DEFERRED;
  </code></pre>
  
  <p>The rows I have added are:</p>
  
  <pre><code>INSERT INTO category VALUES (1, NULL, 'animal');
  INSERT INTO category VALUES (2, 1, 'dog');
  INSERT INTO category VALUES (3, 1, 'cat');
  INSERT INTO category VALUES (4, 3, 'siamese');
  INSERT INTO category VALUES (5, 3, 'persian');
  INSERT INTO category VALUES (6, 7, 'scary1');
  INSERT INTO category VALUES (7, 6, 'scary2');
  </code></pre>
  
  <p>I'm trying to figure out how to recursively query this using <code>WITH RECURSIVE</code> to get a result that looks as such:</p>
  
  <pre><code>   ids   |         names      
  ---------+------------------------
   {1}     | animal
   {2,1}   | dog, animal
   {3,1}   | cat, animal
   {4,3,1} | siamese, cat, animal
   {5,3,1} | persian, cat, animal
   {6,7}   | scary1, scary2
   {7,6}   | scary2, scary1
  </code></pre>
  
  <p>Where <code>ids</code> is an array containing each parent until the root, and where <code>names</code> is a string with each name separated by commas.
  I also need to handle the paradox condition without hanging.</p>
  
  <p>Can somebody point me in the right direction?</p>