6 added 15 characters in body
source | link

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.

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:

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.

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.

5 One last bit of advice
source | link

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:

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.

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.

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:

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.

4 added 76 characters in body
source | link

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 failsthat's not possible for the saved defaultDEFAULT 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 sybolicsymbolic ip in your code example. Tested in Postgres 10.

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. Postgres makes it work via input / output conversion. But that fails 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 sybolic ip in your code example. Tested in Postgres 10.

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.

3 add more explanation
source | link
2 added 124 characters in body
source | link
1
source | link