On Sun, Aug 31, 2025 at 3:14 AM Jim Jones <[email protected]> wrote: > > Hi > > On 19.08.25 03:35, torikoshia wrote: > > Considering what the user was trying to do, it might be helpful to > > provide a similar hint in this case as well. > > I’ve attached a patch that adds such a hint. > > > > What do you think? > > > > +1 > > I tested the patch and the error message now has the intended HINT ... > > postgres=# COPY t1 TO STDOUT WHERE i > 5; > ERROR: WHERE clause not allowed with COPY TO > LINE 1: COPY t1 TO STDOUT WHERE i > 5; > ^ > HINT: Try the COPY (SELECT ... WHERE ...) TO variant. > > ... which aligns with the HINT from COPY TO from views > > postgres=# COPY v1 TO STDOUT; > ERROR: cannot copy from view "v1" > HINT: Try the COPY (SELECT ...) TO variant.
Thanks for your review! Updated the patch to pass regression tests.
From 90cbddde61c4028a44654005a4e417c74e303d16 Mon Sep 17 00:00:00 2001 From: Atsushi Torikoshi <[email protected]> Date: Mon, 1 Sep 2025 07:35:12 +0900 Subject: [PATCH v2] COPY TO: provide hint when WHERE clause is used Provide a hint suggesting COPY TO when a WHERE clause is specified, similar to the hint already given for views or others. --- 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; -- 2.48.1
