Convert varchar column to inet

Question

I have a table column containing IP addresses, datatype is varchar(255). I want to switch to inet datatype now. Is there a way to convert existing addresses in IP column to inet datatype? Tried:

ALTER TABLE request_logs ALTER COLUMN ip type inet USING ip::inet;
ALTER TABLE request_logs ALTER COLUMN ip type inet USING CAST(ip AS inet);

But no luck, getting:

PG::DatatypeMismatch:
ERROR:  default for column "sign_up_ip" cannot be cast automatically to type inet

All values are '127.0.0.1'. Postgres version is 9.6.9.

Answer 1

The ALTER TABLE statement works for me. The error message indicates that the column sign_up_ip has a DEFAULT value attached to it, which is not valid for the new data type inet. Drop the default of the column first, then convert the data type. Then add a new default (if applicable).

Even if it would seem valid. The manual, chapter "Changing a Column's Data Type":

PostgreSQL will attempt to convert the column's default value (if any) to the new type, as well as any constraints that involve the column. But these conversions might fail, or might produce surprising results. It's often best to drop any constraints on the column before altering its type, and then add back suitably modified constraints afterwards.

There is no cast registered between varchar and inet. That's why you had to add the USING clause with an explicit conversion. Postgres makes it work via input / output conversion. But that's not possible for the saved DEFAULT expression.

You can do it all in a single DDL command to minimize friction with concurrent transactions (if that should matter):

ALTER TABLE request_logs
   ALTER COLUMN sign_up_ip DROP default
 , ALTER COLUMN sign_up_ip type inet USING ip::inet
 , ALTER COLUMN sign_up_ip SET DEFAULT '192.168.1.1';  -- arbitrary example

Using your actual column name sign_up_ip instead of the symbolic ip in your code example. Tested in Postgres 10.

If your column should hold any varchar values not valid as type inet, your conversion still fails, but this time with something like:

ERROR:  invalid input syntax for type inet: "1234.123.123.123"

That's one reason why doing all in a single statement (or at least in a single transaction) is a good idea: Everything is back to the original state after the error and the automatic ROLLBACK.