Re: Partial index with regexp not working

On 11/09/2007, Phoenix Kiula <phoenix.kiula@gmail.com> wrote:
>
> The suggestion in this thread that a regex index will come into play
> only when the WHERE condition specifically mentions it was indeed the
> key for me.



Ok, I've hit a snag about this index. I think it's to do with how my
regex is structured. Basically this column can have either IP
addresses, or alphanumeric user IDs. If it is not an IP address, it is
a registered user ID. What is the best way of ascertaining that a
column value is *not* an IP address?

I tried this:

select * from trader where trader_id !~ '[0-9]+\.[0-9]+\.[0-9]+\.';

And this works, but I wonder if a partial index on a negative
condition ("!~") will be slower than a positive condition?