En 15 Apr 2002 17:34:48 -0400
Timothy Wood <timothy@hallcomp.com> escribió:
> On Mon, 2002-04-15 at 15:46, Tom Lane wrote:
> > Timothy Wood <timothy@hallcomp.com> writes:
> > > SELECT * FROM table WHERE CAST('field' AS TEXT) LIKE '%value%';
> >
> > I think you meant
> >
> > SELECT * FROM table WHERE CAST(field AS TEXT) LIKE '%value%';
>
> Ahh, that does seem to work. Big difference without the single quotes.
> What exactly is the big difference there or what do the single quotes do
> that I was not aware of when I used them?
The difference is that field without quotes is an identifier (eg. a
field name), while a field with single quotes is a literal string. In
your query, Postgres was trying to match 'field' to '%value%' while it
obviously can't.
Note that it's different when you use double quotes. There, they are
used to mean that the identifier should not be case-transformed (sorry,
wrong verb); that way, you can query fields with names like "Field".
> The regular expressions do seem like a better idea, but is there any
> benefit over one or the other aside from the flexibility of the regexps?
Regexes cannot use indexes unless they are anchored at the beggining
(LIKE searches cannot either, but you have to unanchor them explicitly,
which is the opposite as you do with regexes). I dunno if the operations
are optimally implemented; if so, LIKE searches should be way faster as
they are much simpler in the general case (but should be equally fast as
simple regexes)
--
Alvaro Herrera (<alvherre[a]atentus.com>)
"Pensar que el espectro que vemos es ilusorio no lo despoja de espanto,
sólo le suma el nuevo terror de la locura" (Perelandra, CSLewis)