title: One-to-Many relationships in (Postgre)SQL
body: <p>I have two tables:</p>
  
  <p>posts:</p>
  
  <pre><code> id | ... other stuff ... |     tags                         
  ----+---------------------+--------------
    1 |         ...         | &lt;foo&gt;&lt;bar&gt;
    2 |         ...         | &lt;foo&gt;&lt;baz&gt;&lt;blah&gt;
    3 |         ...         | &lt;bar&gt;&lt;blah&gt;&lt;goo&gt;
  </code></pre>
  
  <p>and tags:</p>
  
  <pre><code>     tag         
  --------------
   &lt;foo&gt;
   &lt;bar&gt;
   &lt;baz&gt;
   &lt;blah&gt;
   &lt;goo&gt;
  </code></pre>
  
  <p>posts.tags and tags.tag are both of type text. What I want is a relation from tags.tag to rows in posts such that querying <code>&lt;foo&gt;</code> would give me rows corresponding to posts 1 and 2, querying <code>&lt;blah&gt;</code> gives me 2 and 3, <code>&lt;bar&gt;</code> gives me 1 and 3, etc.</p>
  
  <p>I've looked at foreign keys, but I'm not sure it's what I want. (and honestly, I'm not exactly sure what it does). From what I can tell a foreign key must be equal to a primary key/unique column of a table. But what I want is all rows such that <code>posts.tags ~ '.*&lt;foo&gt;.*'</code>, etc. I also want to be able to, say, get all tags that start with b, eg:</p>
  
  <pre><code>CREATE VIEW startswithB AS
  SELECT tag
  FROM tags
  WHERE tag ~ '&lt;b.*&gt;';
  
  SELECT DISTINCT * FROM posts, startswithB WHERE posts.tags ~ ('.*' || startswithB || '.*');
  </code></pre>
  
  <p>How do I get the relation I'm looking for? Is it possible?</p>
  
  <p>EDIT:</p>
  
  <p>Okay, what I've done:</p>
  
  <p>create post_tags:</p>
  
  <pre><code>SELECT posts.id, tags.tag 
  INTO post_tags 
  FROM posts, tags 
  WHERE posts.tags ~ ('.*' || tags.tag || '.*');
  </code></pre>
  
  <p>select all posts with tag <code>&lt;foo&gt;</code>:</p>
  
  <pre><code>SELECT *
  FROM posts
  WHERE posts.id IN (
      SELECT id
      FROM post_tags
      WHERE tag = '&lt;foo&gt;'
  );
  </code></pre>