In Postgres, is it possible to get all rows from a table where a column can be cast to a different type?

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?

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)

StackExchange Database Administrators Q#127245, answer score: 5