Re: postgresql wildcard when parameter is -1

On Fri, Aug 25, 2006 at 07:42:46AM +0100, ben sewell wrote:
> thanks for your reply. I've seen that % is the wildcard in postgres, so
> couldnt I just do an if statement to overwrite the parameter? Also, I'm
> interested in a wildcard for dates. Would that be %%/%%/%%%%?

The % character is a wildcard in text pattern matching; if you want
to use it to match non-text data then you'll have to cast that data
to text. Many common types have implicit casts to text so you might
be able to rely on that, although an explicit cast would make your
intentions more clear. However, now all your expressions would
have to use LIKE, which won't use non-text indexes. You could
address that by creating indexes on cast-to-text expressions, but
that has implications on storage space and performance of inserts,
updates, and deletes. You'll also have to consider whether pattern
matching instead of exact matching might yield unexpected results.

In pattern matching the % character matches zero or more characters,
so if your date output format is dd/mm/yyyy or mm/dd/yyyy then %/%/%
should work, or for yyyy-mm-dd then %-%-%. The underscore (_)
matches exactly one character, so you could also use __/__/____ or
____-__-__.

--
Michael Fuhr