Jagadeesh Panuganti: Storing Network Addresses in Oracle vs PostgreSQL vs SQL Server

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 re[...]