We're Moving!

The Vertica Forum is moving to a new OpenText Analytics Database (Vertica) Community.

Join us there to post discussion topics, learn about

product releases, share tips, access the blog, and much more.

Create My New Community Account Now


Sorting IP addresses in Vertica — Vertica Forum

Sorting IP addresses in Vertica

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!

Sign In or Register to comment.