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
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
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: "118.104.22.168"
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