postgres.git / summary / log / commit / refs

commit    229911c4bf9a4acfb2b81cff148fd8391e0a577b
Author:   Fujii Masao <fujii@postgresql.org>
Date:     Tue Sep 02 23:33:54 2025 +0000

    Add HINT for COPY TO when WHERE clause is used.
    
    COPY TO does not support a WHERE clause, and currently fails with the error:
    
        ERROR:  WHERE clause not allowed with COPY TO
    
    Since the intended behavior can be achieved by using
    COPY (SELECT ... WHERE ...) TO, this commit adds a HINT
    to the error message:
    
        HINT:  Try the COPY (SELECT ... WHERE ...) TO variant.
    
    This makes the error more informative and helps users
    quickly find the alternative usage.
    
    Author: Atsushi Torikoshi <torikoshia@oss.nttdata.com>
    Reviewed-by: Jim Jones <jim.jones@uni-muenster.de>
    Discussion: https://postgr.es/m/3520c224c5ffac0113aef84a9179f37e@oss.nttdata.com


 src/backend/parser/gram.y           | 1 +
 src/test/regress/expected/copy2.out | 1 +
 2 files changed, 2 insertions(+)

diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index db43034b9db..9fd48acb1f8 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -3442,6 +3442,7 @@ CopyStmt:	COPY opt_binary qualified_name opt_column_list
 						ereport(ERROR,
 								(errcode(ERRCODE_SYNTAX_ERROR),
 								 errmsg("WHERE clause not allowed with COPY TO"),
+								 errhint("Try the COPY (SELECT ... WHERE ...) TO variant."),
 								 parser_errposition(@11)));
 
 					n->options = NIL;
diff --git a/src/test/regress/expected/copy2.out b/src/test/regress/expected/copy2.out
index caa3c44f0d0..f3fdce23459 100644
--- a/src/test/regress/expected/copy2.out
+++ b/src/test/regress/expected/copy2.out
@@ -163,6 +163,7 @@ COPY x TO stdout WHERE a = 1;
 ERROR:  WHERE clause not allowed with COPY TO
 LINE 1: COPY x TO stdout WHERE a = 1;
                          ^
+HINT:  Try the COPY (SELECT ... WHERE ...) TO variant.
 COPY x from stdin WHERE a = 50004;
 COPY x from stdin WHERE a > 60003;
 COPY x from stdin WHERE f > 60003;


[parent: 510777a2d56b]