Convert IP address in PostgreSQL to integer?

Question

Is there a query that would be able to accomplish this?

For example given an entry '216.55.82.34' ..I would want to split the string by the '.'s, and apply the equation:

IP Number = 16777216*w + 65536*x + 256*y + z where IP Address = w.x.y.z

Would this be possible from just a Query?

Answer 1

You can simply convert inet data-type to bigint: (inet_column - '0.0.0.0'::inet)

For example:

SELECT ('127.0.0.1'::inet - '0.0.0.0'::inet) as ip_integer

will output 2130706433, which is the integer representation of IP address 127.0.0.1

Answer 2

You can use split_part(). For example:

CREATE FUNCTION ip2int(text) RETURNS bigint AS $$ 
SELECT split_part($1,'.',1)::bigint*16777216 + split_part($1,'.',2)::bigint*65536 +
 split_part($1,'.',3)::bigint*256 + split_part($1,'.',4)::bigint;
$$ LANGUAGE SQL  IMMUTABLE RETURNS NULL ON NULL INPUT;


SELECT ip2int('200.233.1.2');
>> 3370713346

Or, if don't want to define a function, simply :

SELECT split_part(ip,'.',1)::bigint*16777216 + split_part(ip,'.',2)::bigint*65536 +
 split_part(ip,'.',3)::bigint*256 + split_part(ip,'.',4)::bigint;

The drawback of the later is that, if the value is given by some computation instead of being just a table field, it can be inefficient to compute, or ugly to write.

Answer 3

PG 9.4

create or replace function ip2numeric(ip varchar) returns numeric AS
$$
DECLARE
  ip_numeric numeric;
BEGIN
  EXECUTE format('SELECT inet %L - %L', ip, '0.0.0.0') into ip_numeric;

  return ip_numeric;
END;
$$ LANGUAGE plpgsql;

Usage

select ip2numeric('192.168.1.2');
$ 3232235778