3

I have a table in a Postgres DB that has a TEXT column, named data, that I use to store both IP addresses and domain names. I'm wondering if it's possible to select all the rows in the table whose data column is a valid IP address.

Can this be done, say with a WHERE clause based on a type cast?

3 Answers 3

5

You can create a function for this:

create function is_valid_ip(p_data text)
  returns boolean
as
$$
begin
  return p_data::inet is not null;
exception 
  when others then return false;
end;
$$
language plpgsql;

This function has the advantage that the same validation is done when casting and when filtering the rows.

BUT this will be quite slow, because an exception block in PL/pgSQL is quite expensive and catching one is even more expensive. So if you have a large number of invalid addresses this is going to be really slow.

You can use the function like this:

select *
from the_table
where is_valid_ip(address)

Another option is to use a regular exception for this:

create function is_valid_ip(p_data text)
  returns boolean
as
$$
  select p_data ~ '[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}';
$$
language sql;

This function will perform a lot faster than the one that uses casting, but has the downsize that it uses a slightly different rule for checking if the IP address is valid. The regex only check IPv4 addresses, so will most probably also want to extend the regex to check for IPv6 addresses as well (which the cast will do automatically)

2
  • This would certainly work, but do you know if it'd be faster than simply putting the regex comparison in the WHERE clause? (Just curious from an academic standpoint) Jan 25, 2016 at 23:48
  • @JoishiBodio: the regex in the where clause would probably be slightly faster because the overhead of calling a function isn't there. But then, SQL function might be "inlined" so maybe there isn't any overhead. If this is a quick one-time thing, I wouldn't use the function. If that is something that is used a lot, I would use the function, so that I have a central place where the check is maintained.
    – user1822
    Jan 26, 2016 at 6:54
2

You can't apply a type cast in a WHERE clause .. you'll get a cast error because it will try to cast the entire column (instead of a subset of the column).. HOWEVER, you can apply some type of FILTER in the WHERE clause, and then CAST the result of your SELECT.. Postgres will apply the where before the cast, so (as long as your filter is correct) you will avoid a casting issue.

Example (although there are probably other ways to do it than a regex):

CREATE TABLE TAB1 (
  ID BIGSERIAL,
  NOTES TEXT -- MIGHT CONTAIN IP ADDRESSES, BUT COULD CONTAIN ANY TEXT
);

SELECT
  ID,
  NOTES::INET AS ip_address
FROM TAB1
WHERE NOTES ~ '[REGEX HERE FOR IPV4 OR IPV6 - WHATEVER YOU NEED]';

Again - If you try to apply it in a WHERE, you'll get a casting error. It MAY be possible to do that and have some special type of "if error ignore" statement, but I'm not familiar with what that might be.

If the above query is slow, you could improve the speed of it by creating a partial index for it to use.

CREATE INDEX tab1_ip_idx ON TAB1 WHERE NOTES ~ '[EXACT SAME REGEX YOU USE IN QUERY]';
0

My function is_inet.sql can validate IPv4 or IPv6 address. It has several optimizations to increase the speed.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.