Given my experience in my daily work as a PostgreSQL professional I have the impression that most people are still not aware of the fact that PostgreSQL can handle IP addresses - and network information in general - pretty nicely.
Table of Contents
But, there are actually two data types that can help users to store and verify network data: cidr and inet. Here are some examples:
|
1 2 3 4 5 6 7 8 9 10 11 |
test=# SELECT '192.168.0.45/24'::inet; inet ----------------- 192.168.0.45/24 (1 row) test=# SELECT '192.168.0.45'::cidr; cidr ----------------- 192.168.0.45/32 (1 row) |
The inet type accepts an optional netmask. It is perfect to store entire IP ranges. The cidr type will only hold a single IP address.
Just like every data type in PostgreSQL, cidr and inet will validate their content nicely:
|
1 2 3 |
test=# SELECT '192.168.0.450'::cidr; ERROR: invalid input syntax for type cidr: "192.168.0.450" LINE 1: SELECT '192.168.0.450'::cidr; |
Validating data directly is important if you want to keep bad data out of your system.
Did you know that you can actually do math in PostgreSQL? You can add and subtract numbers from an IP range easily. This is very nice if you want to find the next spare IP address or whatever.
Here is how it works:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
test=# SELECT '192.168.0.45'::cidr + 22; ?column? -------------- 192.168.0.67 (1 row) test=# SELECT '192.168.0.45'::cidr + 220; ?column? ------------- 192.168.1.9 (1 row) test=# SELECT '192.168.0.45'::cidr - 220; ?column? ---------------- 192.167.255.81 (1 row) |
By the way: This kind of magic will also work with IPv6 IPs:
|
1 2 3 4 5 |
test=# SELECT '::1'::cidr + 22; ?column? ---------- ::17 (1 row) |
For the most recent PostgreSQL networking posts, see the networking blog spot.
In case you need any assistance, please feel free to contact us.
In order to receive regular updates on important changes in PostgreSQL, subscribe to our newsletter, or follow us on Facebook or LinkedIn.
You need to load content from reCAPTCHA to submit the form. Please note that doing so will share data with third-party providers.
More InformationYou are currently viewing a placeholder content from Facebook. To access the actual content, click the button below. Please note that doing so will share data with third-party providers.
More InformationYou are currently viewing a placeholder content from X. To access the actual content, click the button below. Please note that doing so will share data with third-party providers.
More Information