Oracle, PostgreSQL and SQL Server support a variety of data types that are very relatable. During the process of migration from Oracle to PostgreSQL and SQL Server to PostgreSQL, we see it is not much of a difficult task in mapping data types between Oracle or SQL Server and PostgreSQL. However, there exists some interesting data types in PostgreSQL that are not otherwise seen in Oracle or SQL Server. Three of such data types are inet, cidr and macaddr. In this article, we shall discuss in detail about storing Network addresses in Oracle vs SQL Server vs PostgreSQL.
Inspecting data before Migration to determine the Target Data types
Our expertise in migrations has helped us implement a variety of best practices for all the customer environments. One of such best practices is about implementing the appropriate target data type. Due to lack of expertise on the after effects of a migration, one might choose a default data type mapping between Oracle and PostgreSQL or SQL Server and PostgreSQL, which might appear when you do a Google Search or when you use Ora2Pg or AWS SCT etc. There are also a lot of tools and articles out there to show default data type mappings. However, none of them provide an idea to inspect the data before choosing a data type. For this reason, a varchar in Oracle may always be mapped to varchar or text in Postgres.
MigOps generally inspects data using home-grown solutions to determine the data type for PostgreSQL. It is based on the type of data in Oracle and SQL Server with advanced performance benefits.
Network Addresses
IP Addresses (IPv4 and IPv6) and MAC addresses are the examples of Network addresses.
Why do we need specialized data types for storing Network Addresses ?
When network addresses are required to be stored in databases, the database may accept incorrect user inputs if there is no strict input checking. Similarly, if there are no operators and functions to support queries on such data, there can be performance implications or additional resources used to perform string based search. For this reason, data types to store network addresses will be extremely helpful.
Let us now proceed further and discuss in detail about storing network addresses in Oracle vs PostgreSQL vs SQL Server in detail.
To get notified when we post such useful articles, Subscribe to our Newsletters today.
Storing Network Addresses in SQL Server
SQL Server does not have a data type that is designed specifically for storing IP addresses. For this reason, a varchar is used to store IP addresses. This would not provide any optimization when we need to search for IP’s within a range as the data is considered as text/strings.
create table store_ip_address(ip_add varchar(18));
insert into store_ip_address values ('192.168.12.11');
insert into store_ip_address values ('192.168.12.11/24');
select * from store_ip_address;
ip_add
--------------------
192.168.12.11
192.168.12.11/24
Storing Network Addresses in Oracle
Similar to SQL Server, Oracle does not have a data type that is designed for storing IP addresses. For this reason, we should again use varchar in the case of Oracle. This might add a performance bottleneck.
create table store_ip_address(ip_add varchar(18)); insert into store_ip_address values ('192.168.12.11'); insert into store_ip_address values ('192.168.12.11/24'); select * from store_ip_address; IP_ADD ---------------------- 192.168.12.11 192.168.12.11/24
Storing Network Addresses in PostgreSQL
When compared to Oracle and SQL server, PostgreSQL has better support for certain types of data. As we are talking about network addresses, yes, we have data types called inet and cidr.
inet accepts zero bits to the right of the netmark whereas cidr does not. Let us consider 192.168.0.3/24 as an example. This input is accessible for inet but not when cidr is used as the data type.
To keep it simple, We can store ipv4 and ipv6 addresses in inet data type. We can give a subnet mask when inserting, if not, it will take 32 as a subnet mask by default.
create schema test_schema; create table test_schema.store_ip_address(ip_add inet); insert into test_schema.store_ip_address values ('192.168.12.11'); insert into test_schema.store_ip_address values ('192.168.12.1'); insert into test_schema.store_ip_address values ('192.168.12.1/24'); insert into test_schema.store_ip_address values ('2001:0db8:85a3:0000:0000:8a2e:0370:7334');
If we only want to insert networks, we can use cidr data type.
create table test_schema.store_cidr_address(cidr_add cidr); insert into test_schema.store_cidr_address values ('192.168.12.0/24');
If we want to store a MAC address, we also have two data types macaddr and macaddr8. Let us create a table to know the usage of these data types. We can insert mac addresses using different formats. Usually they are separated using ‘:’, ‘-’ or ‘.’
create table test_schema.store_mac_address(mac_addr1 macaddr, mac_addr2 macaddr8);
insert into test_schema.store_mac_address values ('40:2C:84:DB:35:2C', '40:2C:84:DB:35:2C:04:05');
insert into test_schema.store_mac_address values ('40-2C-84-DB-35-2C', '40-2C-84-DB-35-2C-04-05');
insert into test_schema.store_mac_address values ('402C.84DB.352C', '402C.84DB.352C.0405');
insert into test_schema.store_mac_address values ('402C84DB352C', '402C84DB352C0405');
postgres=# select * from test_schema.store_mac_address;
mac_addr1 | mac_addr2
------------------+-------------------------
40:2c:84:db:35:2c | 40:2c:84:db:35:2c:04:05
40:2c:84:db:35:2c | 40:2c:84:db:35:2c:04:05
40:2c:84:db:35:2c | 40:2c:84:db:35:2c:04:05
40:2c:84:db:35:2c | 40:2c:84:db:35:2c:04:05
(4 rows)
Let us take a look at some of the Network Address functions and operators as an example.
If you want to check if an ip is in one subnet, we can check that using the following.
postgres=# select '192.168.12.22'::inet << '192.168.12.0/24'::inet; ?column? ---------- t (1 row)
If you want to check if ip is in particular subnet or equal, we should use the following query.
postgres=# select '192.168.12.0/24'::inet <<= '192.168.12.0/24'::inet; ?column? ---------- t (1 row) postgres=# select ip_add, ip_add <<= '192.168.12.0/24'::inet from test_schema.store_ip_address; ip_add | ?column? ------------------------------+---------- 192.168.12.11 | t 192.168.12.1 | t 192.168.12.1/24 | t 2001:db8:85a3::8a2e:370:7334 | f
There are some functions we can use to calculate broadcast address, netmask values etc.
postgres=# SELECT ip_add, broadcast(ip_add), netmask(ip_add) FROM test_schema.store_ip_address; ip_add | broadcast | netmask -----------------+-------------------+----------------- 192.168.12.11 | 192.168.12.11 | 255.255.255.255 192.168.12.1 | 192.168.12.1 | 255.255.255.255 192.168.12.1/24 | 192.168.12.255/24 | 255.255.255.0 (3 rows)
You may refer to some more interesting operators and functions on inet and cidr data types in this article.
Conclusion
As discussed in this article PostgreSQL has interesting data types that are not currently available in Oracle and PostgreSQL. When storing certain types of data such as network addresses in Oracle and SQL server, we have to write our own functions to get the output. In the case of PostgreSQL, they are predefined and they are ready to use. There are several other unknown PostgreSQL capabilities that are important to be considered during the process of migration from Oracle to PostgreSQL and SQL Server to PostgreSQL. Contact MigOps today to have an Assessment performed before or after your migration. You may additionally fill the following form to get in touch with one of our experts.
