diff options
Diffstat (limited to 'src')
| -rw-r--r-- | src/backend/commands/tablecmds.c | 25 | ||||
| -rw-r--r-- | src/test/regress/expected/alter_table.out | 7 | ||||
| -rw-r--r-- | src/test/regress/sql/alter_table.sql | 1 |
3 files changed, 26 insertions, 7 deletions
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index 84dbee0c411..d3947139c07 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -7888,11 +7888,26 @@ ATPrepAlterColumnType(List **wqueue, COERCE_IMPLICIT_CAST, -1); if (transform == NULL) - ereport(ERROR, - (errcode(ERRCODE_DATATYPE_MISMATCH), - errmsg("column \"%s\" cannot be cast automatically to type %s", - colName, format_type_be(targettype)), - errhint("Specify a USING expression to perform the conversion."))); + { + /* error text depends on whether USING was specified or not */ + if (def->cooked_default != NULL) + ereport(ERROR, + (errcode(ERRCODE_DATATYPE_MISMATCH), + errmsg("result of USING clause for column \"%s\"" + " cannot be cast automatically to type %s", + colName, format_type_be(targettype)), + errhint("You might need to add an explicit cast."))); + else + ereport(ERROR, + (errcode(ERRCODE_DATATYPE_MISMATCH), + errmsg("column \"%s\" cannot be cast automatically to type %s", + colName, format_type_be(targettype)), + /* translator: USING is SQL, don't translate it */ + errhint("You might need to specify \"USING %s::%s\".", + quote_identifier(colName), + format_type_with_typemod(targettype, + targettypmod)))); + } /* Fix collations after all else */ assign_expr_collations(pstate, transform); diff --git a/src/test/regress/expected/alter_table.out b/src/test/regress/expected/alter_table.out index 65274bc26b7..3ad2c55775c 100644 --- a/src/test/regress/expected/alter_table.out +++ b/src/test/regress/expected/alter_table.out @@ -1665,7 +1665,7 @@ select f3,max(f1) from foo group by f3; -- Simple tests for alter table column type alter table foo alter f1 TYPE integer; -- fails ERROR: column "f1" cannot be cast automatically to type integer -HINT: Specify a USING expression to perform the conversion. +HINT: You might need to specify "USING f1::integer". alter table foo alter f1 TYPE varchar(10); create table anothertab (atcol1 serial8, atcol2 boolean, constraint anothertab_chk check (atcol1 <= 3)); @@ -1680,7 +1680,10 @@ select * from anothertab; alter table anothertab alter column atcol1 type boolean; -- fails ERROR: column "atcol1" cannot be cast automatically to type boolean -HINT: Specify a USING expression to perform the conversion. +HINT: You might need to specify "USING atcol1::boolean". +alter table anothertab alter column atcol1 type boolean using atcol1::int; -- fails +ERROR: result of USING clause for column "atcol1" cannot be cast automatically to type boolean +HINT: You might need to add an explicit cast. alter table anothertab alter column atcol1 type integer; select * from anothertab; atcol1 | atcol2 diff --git a/src/test/regress/sql/alter_table.sql b/src/test/regress/sql/alter_table.sql index b5ee7b087df..29c1875d2ea 100644 --- a/src/test/regress/sql/alter_table.sql +++ b/src/test/regress/sql/alter_table.sql @@ -1175,6 +1175,7 @@ insert into anothertab (atcol1, atcol2) values (default, false); select * from anothertab; alter table anothertab alter column atcol1 type boolean; -- fails +alter table anothertab alter column atcol1 type boolean using atcol1::int; -- fails alter table anothertab alter column atcol1 type integer; select * from anothertab; |