Sorting IP addresses in Vertica

[Deleted User] · January 2018

This blog post was authored by Jim Knicely.

Often times we store IP addresses in a VARCHAR column in a Vertica table. When querying the data and sorting by the IP address, we see that IP addresses are sorted by its VARCHAR value instead of its numeric value.

Fortunately Vertica has the INET_ATON function which returns an integer that represents the value of the address in host byte order, given the dotted-quad representation of a network address as a string. We can use it to properly sort our IP addresses!

Example:

dbadmin=> \d ip_addresses;
                                       List of Fields by Tables
Schema |    Table     | Column |    Type     | Size | Default | Not Null | Primary Key | Foreign Key
--------+--------------+--------+-------------+------+---------+----------+-------------+-------------
public | ip_addresses | ip     | varchar(15) |   15 |         | f        | f           |
(1 row)

dbadmin=> SELECT * FROM ip_addresses ORDER BY ip;
      ip
---------------
10.0.0.1
10.0.0.2
192.0.0.1
192.120.41.10
255.255.0.0
255.255.255.0
255.255.255.1
65.24.142.126
98.255.43.211
(9 rows)

dbadmin=> SELECT * FROM ip_addresses ORDER BY INET_ATON(ip);
      ip
---------------
10.0.0.1
10.0.0.2
65.24.142.126
98.255.43.211
192.0.0.1
192.120.41.10
255.255.0.0
255.255.255.0
255.255.255.1
(9 rows)

Have fun!