Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitb009823

Browse files
committed
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 ratheroff-target if you had provided a USING clause. Also, it seems helpfulto provide the exact text of a suggested USING clause, so users can justcopy-and-paste it when needed. Per complaint from Keith Rarick and asuggestion from Merlin Moncure.Back-patch to 9.2 where the current wording was adopted.
1 parentb5fe620 commitb009823

File tree

3 files changed

+26
-7
lines changed

3 files changed

+26
-7
lines changed

‎src/backend/commands/tablecmds.c‎

Lines changed: 20 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -7888,11 +7888,26 @@ ATPrepAlterColumnType(List **wqueue,
78887888
COERCE_IMPLICIT_CAST,
78897889
-1);
78907890
if (transform==NULL)
7891-
ereport(ERROR,
7892-
(errcode(ERRCODE_DATATYPE_MISMATCH),
7893-
errmsg("column \"%s\" cannot be cast automatically to type %s",
7894-
colName,format_type_be(targettype)),
7895-
errhint("Specify a USING expression to perform the conversion.")));
7891+
{
7892+
/* error text depends on whether USING was specified or not */
7893+
if (def->cooked_default!=NULL)
7894+
ereport(ERROR,
7895+
(errcode(ERRCODE_DATATYPE_MISMATCH),
7896+
errmsg("result of USING clause for column \"%s\""
7897+
" cannot be cast automatically to type %s",
7898+
colName,format_type_be(targettype)),
7899+
errhint("You might need to add an explicit cast.")));
7900+
else
7901+
ereport(ERROR,
7902+
(errcode(ERRCODE_DATATYPE_MISMATCH),
7903+
errmsg("column \"%s\" cannot be cast automatically to type %s",
7904+
colName,format_type_be(targettype)),
7905+
/* translator: USING is SQL, don't translate it */
7906+
errhint("You might need to specify \"USING %s::%s\".",
7907+
quote_identifier(colName),
7908+
format_type_with_typemod(targettype,
7909+
targettypmod))));
7910+
}
78967911

78977912
/* Fix collations after all else */
78987913
assign_expr_collations(pstate,transform);

‎src/test/regress/expected/alter_table.out‎

Lines changed: 5 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1665,7 +1665,7 @@ select f3,max(f1) from foo group by f3;
16651665
-- Simple tests for alter table column type
16661666
alter table foo alter f1 TYPE integer; -- fails
16671667
ERROR: column "f1" cannot be cast automatically to type integer
1668-
HINT:Specify a USING expressiontoperform the conversion.
1668+
HINT:You might needtospecify "USING f1::integer".
16691669
alter table foo alter f1 TYPE varchar(10);
16701670
create table anothertab (atcol1 serial8, atcol2 boolean,
16711671
constraint anothertab_chk check (atcol1 <= 3));
@@ -1680,7 +1680,10 @@ select * from anothertab;
16801680

16811681
alter table anothertab alter column atcol1 type boolean; -- fails
16821682
ERROR: column "atcol1" cannot be cast automatically to type boolean
1683-
HINT: Specify a USING expression to perform the conversion.
1683+
HINT: You might need to specify "USING atcol1::boolean".
1684+
alter table anothertab alter column atcol1 type boolean using atcol1::int; -- fails
1685+
ERROR: result of USING clause for column "atcol1" cannot be cast automatically to type boolean
1686+
HINT: You might need to add an explicit cast.
16841687
alter table anothertab alter column atcol1 type integer;
16851688
select * from anothertab;
16861689
atcol1 | atcol2

‎src/test/regress/sql/alter_table.sql‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1175,6 +1175,7 @@ insert into anothertab (atcol1, atcol2) values (default, false);
11751175
select*from anothertab;
11761176

11771177
altertable anothertab alter column atcol1 typeboolean;-- fails
1178+
altertable anothertab alter column atcol1 typeboolean using atcol1::int;-- fails
11781179
altertable anothertab alter column atcol1 typeinteger;
11791180

11801181
select*from anothertab;

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp