[torikoshia <torikoshia@oss.nttdata.com>]

Hi,

I saw a user wanted to restrict the rows copied by COPY TO using a WHERE 
clause, but ran into an error.

As you know, COPY FROM supports WHERE, but COPY TO does not:

   =# copy t1 to stdout where i > 5;
   ERROR:  WHERE clause not allowed with COPY TO
   LINE 1: copy t1 to stdout where i > 5;
                           ^

In such cases, we can do with specifying a query as the target of COPY:

   =# copy (select i from t1 where i > 5) to stdout;

However, as shown in the first example, no hint is provided in the error 
message.

For views or others, COPY TO already provides a helpful hint message:

   =# copy v1 to stdout;
   ERROR:  cannot copy from view "v1"
   HINT:  Try the COPY (SELECT ...) TO variant.

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?


-- 
Regards,

--
Atsushi Torikoshi
Seconded from NTT DATA Japan Corporation to SRA OSS K.K.

Attachments:

v1-0001-provide_hint_when_WHERE_is_used.patch (text/x-diff, 945B) download
From d1b4e23a1b24782e9de16d748508c46354e42423 Mon Sep 17 00:00:00 2001
From: Atsushi Torikoshi <torikoshia@oss.nttdata.com>
Date: Sun, 3 Aug 2025 17:55:09 +0900
Subject: [PATCH v1] COPY TO: provide hint when WHERE clause is used

Provide a hint suggesting COPY query TO when a WHERE clause
is specified, similar to the hint already given for views or others.

---
 src/backend/parser/gram.y | 1 +
 1 file changed, 1 insertion(+)

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;
-- 
2.48.1