Improve error message and hint for ALTER COLUMN TYPE can't-cast failure.

We already tried to improve this once, but the "improved" text was rather
off-target if you had provided a USING clause.  Also, it seems helpful
to provide the exact text of a suggested USING clause, so users can just
copy-and-paste it when needed.  Per complaint from Keith Rarick and a
suggestion from Merlin Moncure.

Back-patch to 9.2 where the current wording was adopted.

diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 3f1ea0911ffc88f21c62095a30dc00a710f4992f..90aa786de6f5b66a2a270bce95d3ebc51b11aed5 100644 (file)
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -7321,11 +7321,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->raw_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 77d37f789567a818412cffc94490f7f405f4c687..14fc7ebb530854580698f97b47e961680a7281f1 100644 (file)
--- a/src/test/regress/expected/alter_table.out
+++ b/src/test/regress/expected/alter_table.out
@@ -1660,7 +1660,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));
@@ -1675,7 +1675,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 15d339a5f017b086dec8f614e982c7fbeb9c9d26..55744bfa3d6d5583dbf6b64e9dcdea3c1b3cefa5 100644 (file)
--- a/src/test/regress/sql/alter_table.sql
+++ b/src/test/regress/sql/alter_table.sql
@@ -1174,6 +1174,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;