Re: select from pipe-delimited field

On 08/24/2010 07:07 AM, san man wrote:
> Thanks for the replies.
> David, I would have normalized it to 2 or more tables, but the number of
> bar-delimited are not fixed and as new data are added the maximum number
> of these values may change. Also, the problem with like I think is that
> matching is not strict and thus might give spurious hits.

You are trying to simulate arrays using your own custom setup.

If you switch from using pipe-delimeted text to an array, you can use
the PostgreSQL array operators to do what you want. You even have
(limited) indexing options.

As for normalizing the data out to another table: Sometimes performance
concerns render that undesirable. Arrays can be very useful for fairly
small amounts of data that's tightly associated with a given record,
especially things like search keys.

There's a reason that tsvector is implemented how it is, rather than as
a breakout table full of keyword associations. It has to be fast, and
indexable. It sounds like the OP's problem has the same requirements.

--
Craig Ringer