Re: Partial index with regexp not working

Phoenix Kiula wrote:
>
> 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?

To be honest, I'd probably just have a separate column "uid_type", set
it when creating the user and then just have a partial index WHERE
uid_type='IP'

--
Richard Huxton
Archonet Ltd