postgresql - One-to-Many relationships in (Postgre)SQL -

i have 2 tables:

posts:

 id | ... other stuff ... |     tags                          ----+---------------------+--------------   1 |         ...         | <foo><bar>   2 |         ...         | <foo><baz><blah>   3 |         ...         | <bar><blah><goo> 

and tags:

     tag          --------------  <foo>  <bar>  <baz>  <blah>  <goo> 

posts.tags , tags.tag both of type text. want relation tags.tag rows in posts such querying <foo> give me rows corresponding posts 1 , 2, querying <blah> gives me 2 , 3, <bar> gives me 1 , 3, etc.

i've looked @ foreign keys, i'm not sure it's want. (and honestly, i'm not sure does). can tell foreign key must equal primary key/unique column of table. want rows such posts.tags ~ '.*<foo>.*', etc. want able to, say, tags start b, eg:

create view startswithb select tag tags tag ~ '<b.*>';  select distinct * posts, startswithb posts.tags ~ ('.*' || startswithb || '.*'); 

how relation i'm looking for? possible?

edit:

okay, i've done:

create post_tags:

select posts.id, tags.tag  post_tags  posts, tags  posts.tags ~ ('.*' || tags.tag || '.*'); 

select posts tag <foo>:

select * posts posts.id in (     select id     post_tags     tag = '<foo>' ); 

what have going on here many-to-many relationship. think it: each tag can on several posts, , each post can have several tags.

the correct relational architecture add table in middle this:

create table post_tags (   id integer references posts,   tag varchar references tags ); 

then drop tags column on posts table.

this solves issues, because can set of tags on post or set of posts given tag joining against post_tags in different directions. can list of tags start using regular query, more difficult if have bunch of strings concatenated in 1 field.