PostgreSQL: Convert numeric IP address field to INET field
Recall that an IPv4 address can be represented as a 32-bit integer. In many RDBMSs, this means that a 10-digit numeric field is sufficient. In this case, your application would have to handle the conversion from integer to strings like “127.0.0.1” and back again for
UPDATE operations. But if you’re using PostgreSQL, you can use the
inet field and have the string conversion handled automagically and your heart can rest easy knowing that the address is stored efficiently.
In my case, after a migration from MySQL, I ended up with a
numeric(10) field and I wanted to convert it to a
inet field. Here’s how I did it:
ALTER TABLE my_table ALTER COLUMN ip_address TYPE inet USING '0.0.0.0'::inet + ip_address::bigint;
There is no conversion defined from
ip_address::inet would fail. So here we take advantage of addition being defined for
bigint operands to accomplish the conversion.