From: torikoshia <torikoshia@oss.nttdata.com>
To: pgsql-hackers@lists.postgresql.org
Subject: COPY TO: provide hint when WHERE clause is used
Date: Tue, 19 Aug 2025 10:35:28 +0900
Message-ID: <3520c224c5ffac0113aef84a9179f37e@oss.nttdata.com> (raw)
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.yindex 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: torikoshia@oss.nttdata.com, pgsql-hackers@lists.postgresql.org
Subject: Re: COPY TO: provide hint when WHERE clause is used
In-Reply-To: <3520c224c5ffac0113aef84a9179f37e@oss.nttdata.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox